Steve Bergman wrote:
When I moved this change to the server which is actually going to be
running this (which was Sqlite 3.1.x) the query was still slow even
with the index available. However, upgrading it to 3.3.3 and
ANALYZE'ing it fixed the problem there, as well.
The index is most effective when used to eliminate most records early.
The slow query doesn't do that if it joins the tables in the order given.
Newer versions of SQLite do a better job of reordering the tables in
joins, especially if you have real data available, and run the analyze
command to gather statistics about the tables. After you analyzed the
database the optimizer probably reordered the joins to the same order as
the fast query. You can check this by looking at the query plans for the
two queries.
explain query plan select .....
This will display the tables and indexes that are used to execute the query.
HTH
Dennis Cote
