>
> If you're using prefix queries, consider setting the prefix= option:
> <http://www.sqlite.org/fts3.html#section_6_5>
>
> Try running the optimize command:
> <http://www.sqlite.org/fts3.html#optimize>
>
> Show your FTS queries that are too slow.
>
Both prefix and optimize commands help to some degree, but what bothers
us ist a matter of execution order.
Consider the following:
M is a regular table with id as INTEGER PRIMARY KEY. F1 and F2 are FTS
tables.
If we do
SELECT M.id FROM M
INNER JOIN F1 on (F1.docid = M.id)
INNER JOIN F2 on (F2.docid = M.id)
WHERE F1 MATCH 'Jack'
AND F2 MATCH 'Jill'
this Query will run for quite some time if the condition on F1 matches a
high number of rows (up to millions) even if the F2 condition matches
only a few rows.
Execution time will be dramatically reduced if we rewrite the Query like
this:
SELECT M.id FROM M
INNER JOIN F2 on (F2.docid = M.id)
INNER JOIN F1 on (F1.docid = M.id)
WHERE F1 MATCH 'Jack'
AND F2 MATCH 'Jill'
So what we are looking for is a way to do an estimate on the number of
hits to rearrange the JOINs for better performance. Of course it would
be even nicer if the Query Planner would do this like for regular tables.
Hope you get the idea
Regards
Gerd
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
