sqlsqliteindexingquery-optimizationdatabase-optimization

Very slow queries in a large database even with indexes

I have a large database with login and level columns. All queries were slow before I created indexes on these columns:

CREATE INDEX users_login_index on users(login);
CREATE INDEX users_level_index on users(level);

Now I can quickly find a user by login (it's unique) or many users by level. This query is fast:

SELECT * FROM users WHERE login='somelogin123';

But this query is very slow:

SELECT * FROM users WHERE login='somelogin123' AND level=1;

Why the query becomes slow after adding an additional condition which is also indexed? How to solve my problem?

Solution

  • SQLite might be choosing the wrong index. I suspect that level has only a small number of values. That is usually not a good idea for an index. Try making that a composite index:

    CREATE INDEX users_level_index_login on users(level, login);