[EMAIL PROTECTED] wrote:
"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
that
during INSERT it will be inserted in a sorted order
"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 "directly jumps" to records satisfying all four conditions. That's
why you built an index on all four columns after all.
ii)
Whether INDEXInng based on String( 12 char) gives performace
problem???
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.
iii)
Whether this insertion will be like LINK LIST ( fast) or like
Array(slow)??
SQLite implements indexes as B-trees. Inserting a new record is O(log N)
operation.
iv)
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
text)
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]
-----------------------------------------------------------------------------
