4

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).

  3. Query 2 happens coz I'm using Rails 4, and when I write something like Model.where(...).first, AR will add an order by primary_key to the SQL, is there a way to avoid this?

@MarcusAdams, some thoughts on your answer:

  1. don't quite agree with you about the query plan

    secondary indexes(index_promotion_codes_on_t_id_and_gu_id) already contains primary keys(id). So in theory, that where & order by id asc could be performed with index_promotion_codes_on_t_id_and_gu_id only, no need to read any records at all when doing order by. So in theory(just in theory), MySQL should read index_promotion_codes_on_t_id_and_gu_id, find all that meets the where conditions, then pick the smallest id(since there is a limit 1, only the smallest one is needed), then use that id to retrieve the record. Besides, when order by id desc, the query time is just fine. I'm a bit confused about this, jump to next.

  2. MySQL document explains the meaning of rows as The rows column indicates the number of rows MySQL believes it must examine to execute the query.. http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain_rows.

    I think you are right, there are over 5M records, and about 30K meets the where conditions. If those 30K are distributed equally, then MySQL should only scan couple of hundreds of records before it finds a match, thus saving the order by stuff at all.

    And about query 3, now I understand the explain result and query time (compared to query 2). Since those records are located at the 'bottom' of the 5M records(the first match is at 5628309), order by id desc limit 1 should be really fast coz MySQL should only read a couple of thousands of records(from the last one, in reverse order) before it finds a match.

    Anyway it makes sense to me now :)

  3. What I'm trying to say is, when you write Model.where(...).first, in Rails 3, the sql would be something like select * from something limit 1, while in Rails 4, it becomes select * from something order by id asc limit 1, since this order by id asc is causing me trouble in this particular case, I'm trying to find a way to avoid this order by id asc. Anyway now I know Model.where(...).take should do the trick

migrated from stackoverflow.com Apr 12 '14 at 16:55

This question came from our site for professional and enthusiast programmers.

  • 1
    With MySQL 5.6, they default to use persistent table stats, which don't get updated as often as we are used to in earlier versions of MySQL. So if you get crazy optimization, first try ANALYZE TABLE to force the stats to refresh. That fixes queries in many cases. – Bill Karwin Apr 10 '14 at 22:31
  • 1
    @BillKarwin it makes sense to me now, thanks for help – Dean Winchester Apr 11 '14 at 3:13
2

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:

  1. Retrieve the primary keys for all 33088 records that match the WHERE clause
  2. Search for each of the 33088 records using the primary key and read all the table data for the record
  3. Order all 33088 records by id in a temp table
  4. 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:

  1. Scan the table records in order, following the primary key index
  2. Find the first record that matches the WHERE clause (with estimated distribution, about 137 records before it finds one), then stop

2. 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).

3. How else would you get the first record?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.