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
