Re: [sqlite] SQLite performance for 10 Million Records

[Derrell . Lipman]

"Manzoor Ilahi Tamimy" <[EMAIL PROTECTED]> writes:

> Here Is The Schema For these Tables. 
>
> CREATE TABLE HVH (
> Field1 VARCHAR(8),    IDC  VARCHAR(4), 
> Field3 VARCHAR(2),    Field4 VARCHAR(4), 
> Field5 VARCHAR(7),    Field6 VARCHAR(8), 
> Field7 VARCHAR(1),    Field8 FLOAT);
>
> CREATE TABLE ITM(
> IDC      VARCHAR(4),    ITEMNAME VARCHAR(20),
> COLUMN3  VARCHAR(1),    COLUMN4   VARCHAR(1),
> COLUMN5  VARCHAR(1),    COLUMN6   VARCHAR(1),
> COLUMN7  VARCHAR(1),    COLUMN8   VARCHAR(1),
> COLUMN9  VARCHAR(1),    COLUMN10  VARCHAR(1),
> COLUMN11 VARCHAR(1),    COLUMN12  VARCHAR(1),
> COLUMN13 VARCHAR(1),    COLUMN14  VARCHAR(1),
> COLUMN15 VARCHAR(1),    COLUMN16  VARCHAR(1));
>
> CREATE INDEX index1 ON ITM (IDC);

Ok, so at this point, you have one index, only on ITM(IDC).

> //--------------------------------------------------------------------
>
> TEST 2 ( Disk DB )
>
> Table Names     itm , HVH       
> Number of Records :  itm  &#61664; 5 Million  and HVH &#61664;  less than 
> 10,000
>
> QUERY:
> create index index1 on itm(IDC) 

Now you've created another index *on the same column of the same table*.  That
doesn't help you any.  Instead of that, do

  CREATE INDEX index1 ON HVH(IDC);

and you I expect you'll see dramatically lower select times.

Derrell