SQLite: implementing indexing on a large table leading to slow SELECT queries
I'm working on a personal project and I'm working with SQLite version 3.36.0 and I have a large table with about 1 million records... I've created an index on one of the frequently queried columns, `username`. However, despite the index, my SELECT queries are still taking an unusually long time to execute, especially when filtering by `username`. I used the following command to create the index: ```sql CREATE INDEX idx_username ON users(username); ``` After creating the index, I expected to see a important performance improvement, but when I run the query: ```sql SELECT * FROM users WHERE username = 'john_doe'; ``` I still observe that it takes over 1 second to return results. I've also checked the `EXPLAIN QUERY PLAN` output for this query, which indicates that it's still doing a full table scan instead of using the index: ``` 0|0|0|SEARCH TABLE users USING COVERING INDEX idx_username (username=?); 0.99 ``` I've tried vacuuming the database: `VACUUM;` and also `ANALYZE;` to update the statistics, but the performance hasn’t improved. I'm not sure if there’s something more I should be doing, or if there's an scenario with how the data is structured. Has anyone else faced similar issues with indexing in SQLite? What can I do to resolve this question? I'm working on a CLI tool that needs to handle this. My development environment is Windows.