MySQL silly query plan

I'm using 5.6.15-56-log Percona Server. Here's my table(which has over 5M records)

CREATE TABLE `promotion_codes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,    
  `code` varchar(255) DEFAULT NULL,    
  `template_id` int(11) DEFAULT NULL,    
  `created_at` datetime DEFAULT NULL,    
  `gain_user_id` int(11) DEFAULT NULL,    
  PRIMARY KEY (`id`),    
  UNIQUE KEY `index_promotion_codes_on_code` (`code`),    
  KEY `index_promotion_codes_on_t_id_and_gu_id` (`template_id`,`gain_user_id`),    
) ENGINE=InnoDB AUTO_INCREMENT=5995895 DEFAULT CHARSET=utf8

Query 1 (took 0.00 sec, looks fine):

select * from promotion_codes where template_id = 443 and gain_user_id is null limit 1;

Explain:

+----+-------------+-----------------+------+-----------------------------------------+-----------------------------------------+---------+-------------+-------+-----------------------+
| id | select_type | table           | type | possible_keys                           | key                                     | key_len | ref         | rows  | Extra                 |
+----+-------------+-----------------+------+----------------------------------------------------+----------------------------------------------------+---------+-------------+---------+
|  1 | SIMPLE      | promotion_codes | ref  | index_promotion_codes_on_t_id_and_gu_id | index_promotion_codes_on_t_id_and_gu_id | 10      | const,const | 33088 | Using index condition |
+----+-------------+-----------------+------+-----------------------------------------+-----------------------------------------+---------+-------------+-------+-----------------------+

Query 2 (took 4.46 sec, what the hell?)

select * from promotion_codes where template_id = 443 and gain_user_id is null order by id asc limit 1;

Explain:

```
+----+-------------+-----------------+-------+-----------------------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type

I'm assuming because you have SELECT above, that you are actually using SELECT .

1. If MySQL were to use the index_promotion_codes_on_t_id_and_gu_id index when ordering on id, then MySQL would have to do the following:

• Retrieve the primary keys for all 33088 records that match the WHERE clause

• Search for each of the 33088 records using the primary key and read all the table data for the record

• Order all 33088 records by id in a temp table

• Get the first record

If it were a bubble sort, it would quickly find the first record and could end the sort early, but with the method it uses, it's nearly done sorting before it's sure it has the first record.

Whereas if it uses the primary key (which has the order) it would do the following:

• Scan the table records in order, following the primary key index

• Find the first record that matches the WHERE clause (with estimated distribution, about 137 records before it finds one), then stop

1. 137 is the estimated number of number of rows it will scan before it finds a match. Total rows divided by 33088 matching records approximately equals 137. Strangely, the EXPLAIN results for the query without an ORDER BY doesn't consider LIMIT (the rows count is the total number of records that match the WHERE), but the query with the ORDER BY does consider the LIMIT (the number it expects to scan before finding one).

1. How else would you get the first record?

StackExchange Database Administrators Q#63071, answer score: 2