Can MySQL use primary key values from a secondary index?

In the article about the role of a primary key, I mentioned that a secondary index in an InnoDB table consists not only of the values of its member columns, but also values of the table's primary key are concatenated to the index. I.e. the primary key contents is part of every other index.

Assuming the following table structure:

CREATE TABLE `bets` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `game_id` int(10) unsigned NOT NULL,
...
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB

Here is the visualization:

If MySQL could use in queries these implicitly added values, it would maybe allow to save some space on listing the primary key columns at the end of an index explicitly. Let's check various cases.

Row filtering

mysql> EXPLAIN
    -> SELECT *
    -> FROM   bets
    ->        JOIN games
    ->        ON     games.id = bets.id
    -> WHERE  bets.user_id = 111
    ->        AND bets.id > 3476G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bets
         type: ref
possible_keys: user_id
          key: user_id
      key_len: 4
          ref: const
         rows: 22
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: games
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: game.bets.id
         rows: 1
        Extra:

Both key_len and ref fields indicate that only one four bytes long column is used from the user_id index. MySQL cannot use the primary key values in a secondary index for filtering in WHERE clause.

Sorting with ORDER BY

mysql> EXPLAIN
    -> SELECT   *
    -> FROM     bets
    ->          JOIN games
    ->          ON       games.id = bets.game_id
    -> WHERE    bets.user_id = 111
    -> ORDER BY bets.id DESCG
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bets
         type: ref
possible_keys: user_id
          key: user_id
      key_len: 4
          ref: const
         rows: 22
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: games
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: game.bets.game_id
         rows: 1
        Extra:

Extra only returns Using where, but there is no Using filesort. It means ORDER BY will be optimized using the hidden primary key data from the secondary index.

Aggregating with GROUP BY

mysql> EXPLAIN
    -> SELECT   *
    -> FROM     bets
    ->          JOIN games
    ->          ON       games.id = bets.game_id
    -> WHERE    bets.user_id = 111
    -> GROUP BY bets.idG
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bets
         type: ref
possible_keys: user_id
          key: user_id
      key_len: 4
          ref: const
         rows: 22
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: games
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: game.bets.game_id
         rows: 1
        Extra:

Also in this case Extra neither shows Using filesort nor Using temporary, which would indicate no index is used for grouping. Therefore MySQL can optimize GROUP BY on the concatenated primary key values.

Covering index

mysql> EXPLAIN
    -> SELECT bets.id
    -> FROM   bets
    -> WHERE  bets.user_id = 111G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bets
         type: ref
possible_keys: user_id
          key: user_id
      key_len: 4
          ref: const
         rows: 22
        Extra: Using index

The query execution plan confirms through Using index that it will only need index contents to return result. MySQL can read and return the hidden primary key values to avoid the additional data lookup.

Summary

In InnoDB tables each entry of a secondary index always contains a copy of the corresponding primary key value. These values may in some cases be used to the benefit of query execution plan:

for ORDER BY on the primary key column(s)
for GROUP BY on the primary key column(s)
when returning the primary key column(s) values in the SELECT list

MySQL cannot use them, however, to optimize filtering in WHERE.

Blog