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: [email protected] 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
