Re: SELECT ignoring index even though ORDER BY and LIMIT present

On Wed, 2 Jun 2010, Jori Jovanovich wrote:
> (2) Making the query faster by making the string match LESS specific (odd,
> seems like it should be MORE)

No, that's the way round it should be. The LIMIT changes it all. Consider
if you have a huge table, and half of the entries match your WHERE clause.
To fetch the ORDER BY ... LIMIT 20 using an index scan would involve
accessing only on average 40 entries from the table referenced by the
index. Therefore, the index is quick. However, consider a huge table that
only has twenty matching entries. The index scan would need to touch every
single row in the table to return the matching rows, so a sequential scan,
filter, and sort would be much faster. Of course, if you had an index
capable of answering the WHERE clause, that would be even better for that
case.

Matthew

--
Don't criticise a man until you have walked a mile in his shoes; and if
you do at least he will be a mile behind you and bare footed.