-1

I have a large database. The database has the login and level columns. All queries were very slow before I created indexes on these columns. I used these commands to create indexes:

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

Now I can quickly find a user by the login (it's unique) or many users by the level. For example, this query is fast(works immediately):

SELECT * FROM users WHERE login='somelogin123';

But this query is VERY slow:

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

In the same database. I don't understand why the query becomes slow just after adding an additional condition which is comparing a column which is also been indexed. Explain to me please how this works or what am I doing wrong and how to solve my problem. Hope for your help!

zenno2
  • 11
  • 3

1 Answers1

0

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);
Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624