Why Does the MySQL Query Optimizer choose the secondary index over the clustered primary index?

Question

Why does Mysql optimizer choose the secondary index when doing a 'select * from lookup' with no order by clause.

Is it just a fluke or is this a behind the scenes optimization that assumes since you added a secondary index its more important than the primary key.

I would expect the results to be ordered by primary key as a scan of all the leaf nodes can provide all the data necessary to answer this query.

To reproduce I create a simple key/value pair table (note not auto_increment)

create table lookup (
id int not null,
primary key (id),
name varchar(25),
unique k_name (name)
) engine=innodb;

Insert some data in random non-alphabetical order

insert into lookup values(1, "Zebra"),(2, "Aardvark"),(3, "Fish"),(4,"Dog"),(5,"Cat"),(6,"Mouse");

Query the data (this is where I would expect the data to be returned in order of primary key)

mysql> select * from lookup;
+----+----------+
| id | name     |
+----+----------+
|  2 | Aardvark |
|  5 | Cat      |
|  4 | Dog      |
|  3 | Fish     |
|  6 | Mouse    |
|  1 | Zebra    |
+----+----------+
6 rows in set (0.00 sec)

Where as it is not - it appears that a scan of the k_name leaf nodes has been done. Shown here

mysql> explain select * from lookup;
+----+-------------+--------+-------+---------------+--------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-------------+
|  1 | SIMPLE      | lookup | index | NULL          | k_name | 28      | NULL |    6 | Using index |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-------------+
1 row in set (0.00 sec)

To me this says Mysql is using k_name as a covering index to return the data. If I drop the k_name index then data is returned in primary key order. If I add another un-indexed column data is returned in primary key order.

Some basic information about my setup.

mysql> show table status like 'lookup'\G
*************************** 1. row ***************************
           Name: lookup
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-11-15 10:42:35
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

 mysql> select version();
 +------------+
 | version()  |
 +------------+
 | 5.5.15-log |
 +------------+
 1 row in set (0.00 sec)

Answer 1

In reality, the clustered index (aka gen_clust_index) is populated in an order that has no rhyme or reason other than in rowid order. it is virtually impossible to order the rowids in id order.

In InnoDB, the records in nonclustered indexes (also called secondary indexes) contain the primary key columns for the row that are not in the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

The secondary index governs order. However, each secondary index entry has a primary key entry to the correct row. Also, think of the covering index scenario you mentioned for k_name.

Now, let's switch gears for a moment and discusss the PRIMARY KEY and k_name:

QUESTION : Whose has more columns requested by your original query, the Primary Key or k_name ?

ANSWER : k_name, because it has both name and id in it (id being internal because it is the PRIMARY KEY). The covering index k_name fulfills the query better than the primary key.

Now if the query was SELECT * FROM ORDER BY id, your EXPLAIN PLAN should look like this:

mysql> explain select * from lookup order by id;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | lookup | index | NULL          | PRIMARY | 4       | NULL |    6 |       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+

1 row in set (0.00 sec)

Without specfiying order, the MySQL Query Optimizer picks the index that best fulfills your query. Of course, k_name has the unfair advantage because

• every column in the table is individually indexed
• every column in the table is a Candidate Key
• k_name IS NOT A SECONDARY INDEX because it is a Candidate Key just like the PRIMARY KEY.
• user-defined clustered indexes cannot have the row order altered once established

You cannot manipulate the order of the rows at all. Here is proof of that:

mysql> alter table lookup order by name;
Query OK, 6 rows affected, 1 warning (0.23 sec)
Records: 6  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                           |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1105 | ORDER BY ignored as there is a user-defined clustered index in the table 'lookup' |
+---------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table lookup order by id;
Query OK, 6 rows affected, 1 warning (0.19 sec)
Records: 6  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                           |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1105 | ORDER BY ignored as there is a user-defined clustered index in the table 'lookup' |
+---------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)