unnecssarily sorting whole table

[Alex Kovach]

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.

[Alex Kovach]

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

[Alex Kovach]

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

[Alex Kovach]

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