Ok then... I added 67,600 records like this and still no index use. SQLite version 3.7.2 sqlite> select count(*) from c; 67600 sqlite> explain query plan select achr,bchr from c where achr=bchr; 0|0|TABLE c sqlite> create index c_chr on c(achr,bchr); sqlite> explain query plan select achr,bchr from c where achr=bchr; 0|0|TABLE c Here's my record add: #include <stdio.h> #include <stdlib.h> #include "sqlite3.h" int main() { sqlite3 *db; char *errmsg=NULL; int rc; int i,j,k; sqlite3_open("test.db",&db); rc=sqlite3_exec(db, "CREATE TABLE c (achr char, bchr char)",NULL,NULL,&errmsg); if (rc != SQLITE_OK) { puts(errmsg); sqlite3_free(errmsg); } sqlite3_exec(db,"BEGIN",NULL,NULL,&errmsg); for(k=0;k<100;k++) { for(i=0;i<26;i++) { char sql[4096]; for(j=0;j<26;j++) { sprintf(sql,"INSERT INTO c VALUES ('%c','%c')",'a'+i,'a'+j); rc=sqlite3_exec(db, sql,NULL,NULL,&errmsg); if (rc != SQLITE_OK) { puts(sql); puts(errmsg); sqlite3_free(errmsg); exit(-1); } } } } sqlite3_exec(db,"COMMIT",NULL,NULL,&errmsg); sqlite3_close(db); return 0; }
Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org on behalf of luuk34 Sent: Fri 10/15/2010 7:40 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query On 15-10-10 14:34, Black, Michael (IS) wrote: > I love simple examples like this can help people with understanding > things...so I tried this which I thought would do what Hilmar wants...but > alas....what concept am I missing? > > SQLite version 3.7.2 > sqlite> create table c(achr char,bchr char); > sqlite> create index c_chr on c(achr,bchr); > sqlite> explain query plan select achr,bchr from c where achr=bchr; > 0|0|TABLE c > > Why no use of the index in this case? > because there are no records in the database, so its quicker to read just all records, than to read all record in the order of the index... -- Luuk _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users