[EMAIL PROTECTED] wrote: > > "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] -----------------------------------------------------------------------------
