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.