471,075 Members | 1,276 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

unnecssarily sorting whole table

This simple command always takes 0.30 seconds or longer:

mysql> select foo from footable order by -foo limit 20;

(get the 20 most recent foo's)

This however, always takes 0.0 seconds:

mysql> select foo from footable where foo>'2004-01-10' order by -foo
limit 20;

It looks like in the first example, MySQL is sorting the whole table.
But the column in question is indexed (confirmed by explain select...).
The manual says it should stop at 20 rows because I am using a LIMIT.

foo is of type datetime.

Any ideas how I can get more 0.00 second queries? Giving a start date is
not eloquent, nor can I necessarily give one. It doesn't seem like I
should need to, either.

Thanks,
A.K.
Jul 19 '05 #1
3 1508
Alex Kovach wrote:
This simple command always takes 0.30 seconds or longer:

mysql> select foo from footable order by -foo limit 20;


wow. FWIW, the solution was:

mysql> select foo from footable order by foo desc limit 20;

Makes sense now, index wouldn't apply before..
Jul 19 '05 #2
Alex Kovach wrote:
This simple command always takes 0.30 seconds or longer:

mysql> select foo from footable order by -foo limit 20;


wow. FWIW, the solution was:

mysql> select foo from footable order by foo desc limit 20;

Makes sense now, index wouldn't apply before..
Jul 19 '05 #3
Alex Kovach wrote:
This simple command always takes 0.30 seconds or longer:

mysql> select foo from footable order by -foo limit 20;


wow. FWIW, the solution was:

mysql> select foo from footable order by foo desc limit 20;

Makes sense now, index wouldn't apply before..
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.