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