MySQL Secondary Index Does not Contain Sorted Primary Key Data

MySQL & MariaDB Percona Distribution for MySQL
,
1

In the documentation, it says

“In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.”

This implies to me that the primary key data is appended to the secondary index where the data is already sorted. But when I use a leftmost prefix of a composite index only then sort the result by primary key, the optimizer wants to sort the data which seems to imply that the primary key is not appended to the secondary index?

Setup:

CREATE TABLE `test` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `Name` (`Name`),
  KEY `Name_CountryCode` (`Name`, `CountryCode`)
) ENGINE=InnoDB;

INSERT INTO test VALUES (1, 'a', 'b');
INSERT INTO test VALUES (2, 'c', 'd');

Query 1: Use secondary index ‘Name’, then sort by primary key

mysql> EXPLAIN SELECT * FROM test WHERE Name = 'a' ORDER BY ID\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: Name,Name_CountryCode
          key: Name
      key_len: 140
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
  • As expected, there is no filesort in the Extra field since the primary key is already appended to the secondary index

Query 2: Use the composite index ‘Name_CountryCode’, then sort by primary key

mysql> EXPLAIN SELECT * FROM test USE INDEX (Name_CountryCode) WHERE Name = 'a' ORDER BY ID\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: Name_CountryCode
          key: Name_CountryCode
      key_len: 140
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index; Using filesort
1 row in set, 1 warning (0.00 sec)
  • shouldn’t there be no filesort here since the primary key is already appended to the secondary index?
1 Like
2

For query 2, add CountryCode = X to the WHERE clause and see what happens.

1 Like
3

Well, the data is stored sorted by the primary key and secondary indexes have the value of the column and a pointer to the primary key.

1 Like
4

As @matthewb pointed out, if you add CountryConde you can see that MySQL is able to avoid filesort:

mysql> EXPLAIN SELECT * FROM test USE INDEX (Name_CountryCode) WHERE Name = 'a' AND CountryCode='b' ORDER BY ID\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: Name_CountryCode
          key: Name_CountryCode
      key_len: 152
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

Why is that? Because the PK is added at the end of the index, and MySQL will use an index only if it’s for leftmost prefix. For example an index on columns (A,B,C) can be used to satisfy the combinations (A), (A,B), (A,B,C) but not (B) or (B,C).

You can see how the combinations are in mysql.innodb_index_stats, the column stat_description shows the different ways to use the index:

mysql> select * from mysql.innodb_index_stats  where table_name='test' and index_name='Name_CountryCode';
+---------------+------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name       | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| testing       | test       | Name_CountryCode | 2022-07-08 13:02:10 | n_diff_pfx01 |          2 |           1 | Name                              |
| testing       | test       | Name_CountryCode | 2022-07-08 13:02:10 | n_diff_pfx02 |          2 |           1 | Name,CountryCode                  |
| testing       | test       | Name_CountryCode | 2022-07-08 13:02:10 | n_diff_pfx03 |          2 |           1 | Name,CountryCode,ID               |
| testing       | test       | Name_CountryCode | 2022-07-08 13:02:10 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| testing       | test       | Name_CountryCode | 2022-07-08 13:02:10 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
5 rows in set (0.00 sec)

I hope this helps you.

2 Likes