| SELECT * FROM thistable ORDER BY date [message #1729] |
Mon, 03 September 2007 16:49  |
 sdeluca
Messages: 4
Registered: September 2007
Location: France |
Junior Member |
|
|
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
Come and enjoy http://mobilezoo.biz and http://boursomac.com
|
|
|
|
| Re: SELECT * FROM thistable ORDER BY date [message #1732 is a reply to message #1730 ] |
Tue, 04 September 2007 08:16  |
sdeluca
Messages: 4
Registered: September 2007
Location: France |
Junior Member |
|
|
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.
Come and enjoy http://mobilezoo.biz and http://boursomac.com
|
|
|
| Re: SELECT * FROM thistable ORDER BY date [message #1753 is a reply to message #1732 ] |
Wed, 05 September 2007 10:26 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
Large limits can get really slow.
Executing LIMIT 10000, 1
MySQL has to traverse 10000 rows (by index) and throw them away and when get one row and return to you. Not to mention it will do data read for each row even than index only lookup could be possible.
MySQL does use index though.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
|
Members
Search
Help
Register
Login
Home
