Why different primary key queries have huge speed difference in innodb?

Question

I have a simple table Test:

• id, primary key;
• id2, index;
• and other 50+ all kinds of type columns;

And I know that if I select id from Test, it'll use secondary index id2 rather that primary index (clustered index) as stated in this post.

If I force queries using primary index, why do the results time differ a lot when selecting different columns?

Query 1

select id, url from Test order by id limit 1000000, 1, uses only 500ms+ and here is the explain:

MySQL [x]> explain select id, url from Test order by id limit 1000000, 1;                                                                                                                                                                                        
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | Test      | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000001 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Query 2

select * from Test order by id limit 1000000, 1 uses only 2000ms+, and here is the explain:

MySQL [x]> explain select * from Test order by ID limit 1000000, 1;                                                                                                                                                                                                     
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | Test      | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000001 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

I don't see any difference between both explains. So why is there such a huge difference regarding result time, since they use the same clustered index?

Answer 1

Ok, I found the reason finally... It's because the implementation of mysql limit. (sorry that I just found this Chinese explanation, no English version)

In Query1 and Query2 above, here is what limit do:

1. Mysql query the clustered index, get the first row;
2. Mysql will convert the first row to result;
3. then before sending it to the client, Mysql finds that there is a limit 1000000, so the first row is not the right answer...
4. Mysql then just go to the 2nd row and convert it to result;
5. then before sending it to the client, Mysql finds that there is a limit 1000000, so the second row is not the right answer...;
6. again and again, till it findss the 1000001th row, after converting it to result, it matches the limit 1000000, 1 clase;
7. so finally, this is the right answer, and send it to the client;

However, it has converted totally 1000000 rows. So in the above question, it's the cost between 'all fields conversion(select *) multiply 1000000 rows' vs. 'one/two field conversion(select id/url) multiply 1000000 rows'. No doubt that the former is far slower than the latter.

Don't know why mysql limit behaives so clumsy, but it just is...