Assume you have a table with millions of rows with a date column ranging from 2000 to today
the optimizer would assume, that a select first * from table where date < '2011-01-01' will match 90% of the rows, so its decision is to ignore the index on the date column.
My wish: based on the "first" it should forecast a match of 1 row and should use the index.
Might by difficult to implement, but it would be worth the effort ;-)
asked 20 May '11, 12:31
If you do not specify an ORDER BY clause on the query the optimizer does not know which "first" you wish to get... so it can use whatever plan it wants to give you a row. If you put an "ORDER BY date" on your query I would suspect that it will do an index scan (but I did not confirm this - exercise left to the reader :)
answered 20 May '11, 13:21