CODEWITHSUNDEEP
performancesqlitequery-optimizationexists
Franz D.
Franz D.

Reputation: 1061

Speed up SQLite 'SELECT EXISTS' query

I've got a 3GB SQLite database file with a single table with 40 million rows and 14 fields (mostly integers and very short strings and one longer string), no indexes or keys or other constraints -- so really nothing fancy. I want to check if there are entries where a specific integer field has a specific value. So of course I'm using

SELECT EXISTS(SELECT 1 FROM FooTable WHERE barField=?)

I haven't got much experience with SQLite and databases in general and on my first test query, I was shocked that this simple query took about 30 seconds. Subsequent tests showed that it is much faster if a matching row occurs at the beginning, which of course makes sense.

Now I'm thinking of doing an initial SELECT DISTINCT barField FROM FooTable at application startup, and caching the results in software. But I'm sure there must be a cleaner SQLite way to do this, I mean, that should be part of what a DBMS's job right?

But so far, I've only created primary keys for speeding up queries, which doesn't work here because the field values are non-unique. So how can I speed up this query so that it works at constant time? (It doesn't have to be lightning fast, I'd be completely fine if it was under one second.)

Thanks in advance for answering!

P.S. Oh, and there will be about 500K new rows every month for an indefinite period of time, and it would be great if that doesn't significantly increase query time.

Upvotes: 0

Views: 571

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

Adding an index on barField should speed up the subquery inside the EXISTS clause:

CREATE INDEX barIdx ON FooTable (barField);

To satisfy the query, SQLite would only have to seek the index once and detect that there is at least one matching value.

Upvotes: 3

Related Questions