> "SELECT  * from database where STRING1 = 'x' && STRING2 = 'y' && STATE = 1 
> && purpose = 'z'"
> "Create INDEX  INDX on database( STRING1, STRING2, state, purpose)" 
> i)
>         If above index is present then the SELECT statement given, 
> iterates to the all rows present in the table or it directly jumps to the 
> block where entries             satisfying STRING1 = 'x'  ??

It does a binary search to locate the correct entry.

> ii)
>         Whether INDEXInng based on String( 12 char) gives performace 
> problem???

Indexing a strings is no more expensive (byte for byte) than indexing on 
integers, on average.

> iii)
>         Whether this insertion will be like LINK LIST ( fast) or like 
> Array(slow)??

Insert will be O(logN)

> iv)
>         Iis there any other way to call Select statement with less 
> performance issue?

If any of STRING1, STRING2, State, or purpose contains only
distinct values (or nearly so) then you can index just that one
column for a small performance gain on inserts, with probably
a smaller performance loss on queries.

> Query 2)
> What will be the performance issue if I create INDEX with PATH( Long text) 
> in ASC or DSC mode for the above table????

There is no performance hit for ASC versus DESC. 

DESC is ignored on indices unless your database is created 
as file-format 4. File-format 4 was the default for SQLite
version 3.3.0 through 3.3.6, but because of problems with 
legacy code, file-format 1 became the default again in version 
3.3.7.  You can select file-format 4 using a PRAGMA when you 
create the database.  Or you can make file-format 4 the default 
at compile time.  

D. Richard Hipp  <[EMAIL PROTECTED]>

To unsubscribe, send email to [EMAIL PROTECTED]

Reply via email to