CODEWITHSUNDEEP
c++sqlite
nagaraj sherigar
nagaraj sherigar

Reputation: 87

Select query optimization with multi-column index

A query is taking long time to execute. This is my table structure:

CREATE TABLE mytable(
    Id int primary key,
    column1 text ,
    column2 text,
    column3 text,
    column4 BLOB
);

There are around 160,000 rows in the table.

I also have a covering index:

CREATE INDEX index1 ON mytable(column1 ,column2 ,column3 ,column4);

I use 2 select queries:

SELECT column4 from mytable;

SELECT column4 from mytable where column3="xyz";

that take 0.101722 and 0.136498 seconds to execute, respectively.

These are the pragmas I enabled:

sqlite3_exec(db, "PRAGMA auto_vacuum = 1", NULL, NULL, &zErrMsg);
sqlite3_exec(db, "PRAGMA page_size = 32768", NULL, NULL, &zErrMsg);
sqlite3_exec(db, "VACUUM", NULL, NULL, &zErrMsg);
sqlite3_exec(db, "PRAGMA temp_store = MEMORY", NULL, NULL, &zErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &zErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = OFF", NULL, NULL, &zErrMsg);
sqlite3_exec(db, "PRAGMA cache_size = 15000", NULL, NULL, &zErrMsg);

Is there any other way I can speed up the execution?

I am using sqlite api for c++ on an unix machine.

Upvotes: 2

Views: 72

Answers (1)

Dariusz
Dariusz

Reputation: 22241

With index constructed as such:

CREATE INDEX index1 ON mytable(column1 ,column2 ,column3 ,column4);

you are actually performing indexless search. Multicolumn indexes in sqlite work from left to right only.

If you have a query that filters only on column3 you have to have an index which starts with column3. Like this:

CREATE INDEX index1 ON mytable(column3);

Details on this mechanism can be found in chapter 1.6 in query planning.

Upvotes: 2

Related Questions