Why is SQLite query on two indexed columns so slow?

Question

I have a table with around 65 million rows that I'm trying to run a simple query on. The table and indexes looks like this:

CREATE TABLE E(
    x INTEGER,
    t INTEGER,
    e TEXT,
    A,B,C,D,E,F,G,H,I,
    PRIMARY KEY(x,t,e,I)
);
CREATE INDEX ET ON E(t);
CREATE INDEX EE ON E(e);

The query I'm running looks like this:

SELECT MAX(t), B, C FROM E WHERE e='G' AND t <= 9878901234;

I need to run this queries for thousands of different values of t and was expecting each query to run in a fraction of a second. However, the above query is taking nearly 10 seconds to run!

I tried running the query plan but only get this:

0|0|0|SEARCH TABLE E USING INDEX EE (e=?)

So this should be using the index. With a binary search I would expect worse case only 26 tests, which I would be pretty quick.

Why is my query so slow?

Answer 1

Each table in a query can use one index. Since your WHERE clause looks at multiple columns, you can use a multi-column index. For these, all but the last column used from the index has to test for equality; the last one used can be used for greater than/less than.

So:

CREATE INDEX e_idx_e_t ON E(e, t);

should give you a boost.

For further reading about how Sqlite uses indexes, the Query Planner documentation is a good introduction.

You're also mixing an aggregate function (max(t)) and columns (B and C) that aren't part of a group. In Sqlite's case, this means that it will pick values for B and C from the row with the maximum t value; other databases usually throw an error.