"SELECT  * from database where STRING1 = 'x' && STRING2 = 'y' &&
STATE = 1 && purpose = 'z'"

To achive this whether I need to attach given INDEX with the table so
during INSERT it will be inserted in a sorted order
"Create INDEX  INDX on database( STRING1, STRING2, state, purpose)"

       If above index is present then the SELECT statement given,
iterates to the all rows present in the table or it directly jumps to
block where entries             satisfying STRING1 = 'x'  ??

It "directly jumps" to records satisfying all four conditions. That's why you built an index on all four columns after all.


       Whether INDEXInng based on String( 12 char) gives performace

Any index is a tradeoff: you speed up selects but slow down inserts and updates. There is no problem with indexing strings specifically, if that's what you are asking.


       Whether this insertion will be like LINK LIST ( fast) or like

SQLite implements indexes as B-trees. Inserting a new record is O(log N) operation.

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

The index you built gives best possible performance for this particular query. Whether the tradeoff was worth it only you can decide.

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

None whatsoever - see http://sqlite.org/lang_createindex.html. SQLite ignores ASC and DESC modifiers and always builds an index in ascending order. However, a B-tree can be equally easily walked backwards as forwards, so something like ORDER BY Path DESC can still use the index.

Igor Tandetnik

To unsubscribe, send email to [EMAIL PROTECTED]

Reply via email to