Thank you for spelling out that only one index can be used per query - probably a basic principle for sqlite experts, but one that had passed me by!
The following tuning guide mentions that "if there is a choice of indexes, the query optimizer may make a bad choice": http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#indexes Perhaps my original query hung until the Season index was removed, because it was masking a more useful one? Thanks again for your invaluable help. > That's a true statement in general - having indexes on columns that > frequently appear in your WHERE clauses will make the queries > referencing those columns faster. (That's what an index does, right?) > But once again, the catch is that only a single index can be used by a > given query, so if you want to reference more than one column in your > WHERE clause, and you want that query to be satisfied using an index, > you have to have an index that covers all the referenced columns in a > single index. > I think the common misconception is that indexes on multiple > individual columns can be used in conjunction with one another, which > isn't the case (unless you're talking about bitmap indexes, but since > SQLite doesn't have those, we clearly aren't :P). Once you get > comfortable with the fact that only a single index can be used > everything makes sense. > -Tom Send instant messages to your online friends http://uk.messenger.yahoo.com
