vendredi 11 septembre 2015

Sqlite3 select query optimization

Sqlite select query is taking long time 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);

These are the 2 select queries am using

SELECT column4 from mytable;

SELECT column4 from mytable where column3="xyz";

time taken 0.101722 and 0.136498 seconds respectively.

These are the pragma's 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 unix machine.

Aucun commentaire:

Enregistrer un commentaire