> I was puzzled that removing the single-column index on > Season actually enabled the original query to > complete, having read somewhere in the sqlite docs > that indexing columns used in WHERE conditions > improves performance. Is this something to do with the
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
