On 25/02/2011, at 2:23 AM, Mohit Sindhwani <[email protected]> wrote:
> Thank you for your suggestions - I tried the approach using LIKE and it
> seemed quite slow (this was on Windows CE).. FTS3/4 seem good to explore.
Yes, like '%name%' will be slow since it can't use an index. However, like
'name%' will use an index (if the column has one).
So I suggest something like:
create table Person
( ID integer primary key not null
, "First Name" text collate nocase
, "Middle Name" text collate nocase
, "Last Name" text collate nocase
)
;
create index "Person First Name"
on Person ("First Name")
;
create index "Person Middle Name"
on Person ("Middle Name")
;
create index "Person Last Name"
on Person ("Last Name")
;
Then search like this:
select * from Person where "First Name" like 'Mick%' or "Middle Name" like
'Mick%' or "Last Name" like 'Mick%';
Tom
BareFeetWare
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
