Performing VACUUM on my DB significantly improves query performance. While trying to determine why this is, I found that sqlite3 isn't using the index on the DB in its original state, just a generic SEARCH TABLE.
QUERY PLAN
|--SCAN TABLE data <--- no Index
|--USE TEMP B-TREE FOR GROUP BY
`--USE TEMP B-TREE FOR ORDER BY
After performing VACUUM, the QUERY PLAN shows a SEARCH USING INDEX as it should
QUERY PLAN
|--SEARCH TABLE data USING INDEX index_name (name=?)
|--USE TEMP B-TREE FOR GROUP BY
`--USE TEMP B-TREE FOR ORDER BY
How can I determine why the index isn't being used before the vacuum operation?
I have the explain results as well, but I'm not sure they'd be useful. They are clearly different (original, non-vacuumed result performs a Rewind/Loop where the vacuumed DB OpenRead's the index)
Thank you,
