speed up like wildcard % search in sqlite

[georgiecasey]

Hey,

I've a phonebook app with 180k records of names, addresses and numbers. I search my sqlite database every time the user changes text in the searchbar with this query [select id from phonebook where name like 'car%' and state='ny' limit 10]

for states with loads of entries, the search can be very slow as sqlite has to go through more records to find a match. also, if you search for 'a%' the search is quick but 'z%' it's slow as sqlite has to travel to the end of the records to find 10 matches.

i was wondering does anyone have tips to speed up the search.

[stopwatch]

SQLite Optimization FAQ

see 5.5.1

[whizzconsult]

Hi, I am looking to develop similiar code and was wondering if you could let me know where to find examples of utilizing search with sqlite and returning result sets. Of course for iPhone apps.

Thanks

Lee