Secondary Index on InnoDB Table

MySQL Tutorials - Herong's Tutorial Examples

∟InnoDB Storage Engine - Primary and Secondary Indexes

∟Secondary Index on InnoDB Table

This section describes what is secondary index on an InnoDB table. Any any non-primary index is called secondary index.

If you have a foreign key in an InnoDB table, and want to retrieve rows with a given foreign key, you should create a secondary index (non-primary index) on foreign key column.

"Secondary Index" is a term used in the MySQL reference manual. It states that "each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered (primary) index".

Let's continue with the previous tutorial and validate what is stated in the reference manual.

1. Re-create the "Body" table with 6400 rows and add a secondary index called "SECOND" on the "fid" column.

mysql> ...

mysql> select count(*) from Body;
  |     6400 |

mysql> show index from Body;
| Non_unique | Key_name | Column_name | Null | Index_type |
+------------+----------+-------------+------+------------+
|          0 | PRIMARY  | id          |      | BTREE      |
|          1 | SECOND   | fid         | YES  | BTREE      |

2. Verify index/data pages of both PRIMARY and SECOND indexes. They all should be cached in the buffer.

mysql> select index_name, data_size/number_records as record_size,
    -> count(*) as pages, sum(number_records) as records
    -> from information_schema.innodb_buffer_page
    -> where table_name rlike '`Body`'
    -> group by index_name, data_size/number_records
    -> order by index_name;

  | index_name | record_size | pages | records |
  +------------+-------------+-------+---------+
  | PRIMARY    |     14.0000 |     1 |     801 |
  | PRIMARY    |   1812.0000 |   801 |    6400 |
  | SECOND     |     14.0000 |     6 |    6400 |
  | SECOND     |     18.0000 |     2 |      12 |

The output shows:

801 data pages holding 6400 table rows as part of the PRIMARY index.
1 index page holding 801 data page addresses as part of the PRIMARY index.
6 data pages holding 6400 primary keys as part of the SECOND index.
2 index pages holding 12 data page addresses as part of the SECOND index. This is a surprise for me. It looks like InnoDB is storing 2 copies of the index pages. Why?

3. Compare page usages on retrieving rows with PRIMARY index and SECOND index.

mysql> set @last = 0;
mysql> call GetReads();
mysql> call GetReads();
  | @read | @last    |
  +-------+----------+
  |     0 | 71010383 |

mysql> select * from Body where id=6400;
  | id   | fid  | b1   | b2   | b3   | b4   | b5   | b6   | b7   |
  +------+------+------+------+------+------+------+------+------+
  | 6400 | 6400 | B1   | B2   | B3   | B4   | B5   | B6   | B7   |

mysql> call GetReads();
  | @read | @last    |
  +-------+----------+
  |     2 | 71010385 |

mysql> select * from Body where fid=6400;
  | id   | fid  | b1   | b2   | b3   | b4   | b5   | b6   | b7   |
  +------+------+------+------+------+------+------+------+------+
  | 6400 | 6400 | B1   | B2   | B3   | B4   | B5   | B6   | B7   |

mysql> call GetReads();
  | @read | @last    |
  +-------+----------+
  |    10 | 71010395 |

Okay, InnoDB uses 2 pages: 1 index page and 1 data page, to retrieve the last row with the given PRIMARY key as expected.

But why is InnoDB using 10 pages to retrieve the last row with the given SECOND key? If we follow the secondary key storage logic stated in the MySQL reference manual, InnoDB only need to read 4 pages to retrieve the row:

Read the root index page of "SECOND", and find where is the data page located for "fid=6400".
Read the data page containing the "fid=6400" record, get the associated primary key of "id=6400".
Read the root index page of "PRIMARY", and find where is the data page located for "id=6400".
Read the data page containing the "id=6400" record, get the associated data row of all columns.

See the next tutorial on how to optimize the secondary index.

By the way, here are some key words and terminologies used in MySQL documentation:

PRIMARY KEY - Key word used in SQL language to specify a primary index in the "CREATE TABLE" statement.
PRIMARY - Default name given to the primary index in MySQL server.
Clustered Index - The primary index of an InnoDB table using the "Clustered Index" data structure.
Secondary Index - Any non-primary index of an InnoDB table using a similar data structure as the primary key.