Hi,
the condition in your query
select achr,bchr from c where achr=bchr
involves 2 columns of the table. In this case, an index is useless.
If you do
select achr,bchr from c where achr='foo'
then the index will be used.
Martin
Am 15.10.2010 15:09, schrieb Black, Michael (IS):
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
