I have a database containing a single table, with FTS3:
create virtual table my_fts_table using fts3
(
last_name text not null,
first_name text not null,
middle_name text not null,
identifiers text not null,
address_1 text not null,
city text not null,
state text not null,
zip text not null
);
I loaded a large number of records (20 million) into this table. I then
proceeded to run queries against this table, of the following form:
select rowid from my_fts_table where my_fts_table match @query limit 21
This query (usually) returns very quickly. For example, when @query is
'nicky nigel', the query returns in mere milliseconds (59, and that's as
measured by a web browser client when the server's doing the work). If I
modify the query to be something like 'nicky nigel indiana', the query
suddenly takes MUCH longer. Over 17 seconds, in fact. It returns fewer
rows (2 versus 4), but it seems like it must be searching for each term
individually and then doing the intersection of those results. 'indiana'
of course returns LOTS of rows (7927809 to be exact), because it's not
very selective.
I'm wondering if there's something I'm doing wrong, or whether this is
just an artifact of the FTS3 implementation. I'd love to use it, it's
simple and perfect for my needs (and I'm already using SQLite in this
project so it's a great fit), but having the query suddenly take
"forever" because someone included a state in the search just won't work
for me.
Mark
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
