Optimizer using the first key word

[MCMartin]

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 😉

[Breck_Carter]

Assuming you have ORDER BY the date column, if the index on the date column is clustered then I'll bet $$$ the optimizer will use it.

[MarkCulp · Accepted solution]

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 🙂