0

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  | possible_keys                           | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+----------------------------------------------------+---------+---------+------+------+--+
|  1 | SIMPLE      | promotion_codes | index | index_promotion_codes_on_t_id_and_gu_id | PRIMARY | 4       | NULL |  137 | Using where |
+----+-------------+-----------------+-------+-----------------------------------------+---------+---------+------+------+-------------+

Obviously MySQL did a full table scan so that it took over 4 seconds. And the query time when I force using the index is sane (0.01 sec)

select * from promotion_codes use index (index_promotion_codes_on_t_id_and_gu_id) where template_id = 443 and gain_user_id is null order by id asc limit 1;

Query 3 (took 0.09 sec, oh my)

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

Explain:

+----+-------------+-----------------+-------+-----------------------------------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys                           | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+----------------------------------------------------+---------+---------+------+---------+
|  1 | SIMPLE      | promotion_codes | index | index_promotion_codes_on_t_id_and_gu_id | PRIMARY | 4       | NULL |  138 | Using where |
+----+-------------+-----------------+-------+-----------------------------------------+---------+---------+------+------+-------------+

The explain result is identical to query 2(order by id asc vs order by id desc), however the time it took is far different.

So I have several questions:

  1. Why not use the index when order by id, using that index reduces the rows to scan from over 5M to 30K, so there's just no way that MySQL should ignore that index.

  2. What does the rows column mean in the explain? I always thought it was how many rows MySQL thinks it should scan to get the result, if I'm correct, that in query 2, the 137 is too wrong(the number in query 3 doesn't make sense either).

0

0

Browse other questions tagged or ask your own question.