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?

CodePudding user response：

For the following query:

select id, url from t order by id limit 1000000, 1

MySQL seems to read 1,000,000 rows ordered by id instead of skipping them.

I would suggest changing the query to this:

select * from t where id = (select id from t order by id limit 1000000, 1)

MySQL seems to do a better job at skipping 1,000,000 rows when limit is placed inside a sub query.

CodePudding user response：

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

CodePudding user response：

1. check sql profile，Determine more information

mysql> show profile

2.mysql explain is not very powerful yet.

3.What kind of scene needs limit 10000？