--- michael cuthbertson <[EMAIL PROTECTED]> wrote: > This query: > select * from tbl where num = 2563351070 and > filetype = 'cpp' and > time1 > '2003-01-01' and time1 < '2006-05-05' > returns 18k rows in 600ms and index for time1 is used. > > If I 'plus' the time1 columns, the exec time drops to 420ms and > no index is used. > If I use 'name' instead of 'filetype' with time1 "plussed", no index is > used. > Also, if I use 'order by name', no index is used. > > To summarize, in this query with indexes on most columns, SQLite will only > use time1 index and is slower doing so. > Could someone please explain to me why this happens?
Hitting both the index and the underlying table can often be slower than just doing a table scan if you have to examine every row anyway. I suspect that most rows in your table return true for the condition: time1 > '2003-01-01' and time1 < '2006-05-05', so the index is ineffective in this case. Had your time1 constraint been narrower, then using the time1 index could result in a performance gain. Anyway, you've found an acceptable workaround with +time1. If you think the difference between 420ms and 600ms is significant, perhaps running ANALYZE might help. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
