I downloaded your database and the query above was indeed slow.
But then I ran "ANALYZE" so that SQLite can gather statistics
on the various indices, then reran the query. This time, SQLite
was able to use the ANALYZE results to make better index choices
and the query is quite speedy.
The results of ANALYZE are stored in a special table named
"sqlite_stat1". So you only have to run it once and the result
will be used for all subsequent queries.
Thank, you.
This helps of course. How often should I run analyze? Trying to
figure out how to make this programaticly for new installations. I
guess there has to be a bit of data in the table before I can run
analyze with the outcome that I want?
-- Tobias
