SELECT * FROM thistable ORDER BY date

MySQL & MariaDB Other MySQL® Questions
1

Hi there,

This is my first post, so let me introduce myself. I develop web sites such as http://mobilezoo.biz and http://boursomac.com and I’m really interested in mysql performances.

And I’ve got a question on a simple situation: I’ve got a table ‘thistable’ with an indexed column ‘date’ which has about 90K lines. date is of DATETIME type, the table is using myIsam and has a primary index in addition to an index on date.

When performing EXPLAIN SELECT * FROM thistable ORDER BY date LIMIT 1,100 MySql says it runs through all rows, while still using index ‘date’. I certainly missing something here. Why does it need to look at all rows ?

Thanks in advance,
Sdl

2

This is well known case when EXPLAIN lies.
It will actually only look at one row and send it.

If you’re soring by unindexed column and have “filesort” in explain all rows will really be scanned.

3

Hi Peter,

Thank you very much for your answer.

I had this impression as the explain said using index in the extra but I wasn’t sure.

But, unfortunately, we have a caseof the same req template, where the req takes about 4s (card=670+K lines) as follows:

explain extended SELECT username, user_id FROM phpbb_users ORDER BY user_regdate ASC LIMIT 661600, 50;±—±------------±------------±------±--------------±-------------±--------±-----±-------±------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------------±------±--------------±-------------±--------±-----±-------±------+| 1 | SIMPLE | phpbb_users | index | NULL | user_regdate | 4 | NULL | 672173 | | ±—±------------±------------±------±--------------±-------------±--------±-----±-------±------+1 row in set, 1 warning (0.00 sec)

If we invert the ORDER BY from ASC to DESC and replace the LIMIT boundaries, we’ve got 1e-3 secs:

SELECT username, user_id FROM phpbb_users ORDER BY user_regdate DESC LIMIT 1, 50;

Which gives:

EXPLAIN EXTENDED SELECT username, user_id FROM phpbb_users ORDER BY user_regdate DESC LIMIT 1, 50;±—±------------±------------±------±--------------±-------------±--------±-----±-------±------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------------±------±--------------±-------------±--------±-----±-------±------+| 1 | SIMPLE | phpbb_users | index | NULL | user_regdate | 4 | NULL | 672184 | | ±—±------------±------------±------±--------------±-------------±--------±-----±-------±------+1 row in set, 1 warning (0.00 sec)

Hence the questions:

  • Why does it take so much time when using LIMIT bignumber,tinynumber? To me, it sounds MySql does not actually take advantage of the index on user_regdate. (It certainly read index pages from #1 to #bignumber. When we reverse the boundaries, it will only read from 1 to tinynumber, hence the dramatic speed up.
  • Explain should have said rows= 661600+50 inthe first req and 50 in the second, right ?

What do you think ?

Thanks again for your interest in this question.

4

And explain… yes it could be fixed.

Currently EXPLAIN does not account for LIMIT any how.

It could but it does not )

Optimizer however knows about LIMIT and provides different plan with and without limit.