On 8/07/2009 7:11 PM, aalap shah wrote: > Hi, > > I am a new user to sqlite3, I have a program that searches through a > database. I have a table with 1 column as varchar and I want to > perform a search on it. > I have created an index over that column. And I use a select query > with "column_name LIKE 'a%' ". > So my first question is will this query use the index created or not?
If that is the only index on the table, it should be used. If there is another index on some other column in the table and that other column is mentioned in the query, then SQLite may decide to use that other index instead. You can find out what indexes are being used by using "explain query plan select ..." instead of "select ..."; instead of returning results it will return a row of info for each table involved in the query. Note the above answer is conditional on the expression having a trailing '%'. Anything other than a "startswith" condition can make no use of the index. Reading material: http://www.sqlite.org/optoverview.html > > And if it does then , according to my understanding select query like > above will directly locate records starting with 'a' and results will > be returned. Not "directly" in the sense that a hash index would in a non-LIKE case. All SQLite indexes use a BTree structure. It will locate all rows such that 'a' <= your_column < 'b', typically by descending the tree to locate the smallest value that is >= 'a' then proceding in key sequence until it finds a value that doesn't start with 'a'. > And if I change my query to have "column_name LIKE 'ab%' > " will take more time then previous because sqlite3 will have to > perform strcmp of some sort to find results. No, it will use the same procedure as the LIKE 'a%' query. > But the results that I have observed , it seems that 2nd query takes > less time than first one. Ummm, that could be because logic guarantees that (# rows retrieved by LIKE 'a%') <= (#rows retrieved by LIKE 'ab%') ... more character comparisons, but their cost is trivial compared to the cost per row retrieved. > > And if it doesn't then, how do I make use of index with LIKE queries. See section 4.0 of the link I quoted above. HTH, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
