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

Reply via email to