On Feb 5, 2010, at 6:03 AM, Nasron Cheong wrote:
> Not sure if this is intentional, but it looks like the wrong index
> is being
> selected on fts tables when a rowid is involved.
>
> Given a table:
>
> CREATE VIRTUAL TABLE MessagesFts USING fts3(Message);
>
> Explain query plan using sqlite 3.6.21:
>
> sqlite> EXPLAIN QUERY PLAN SELECT * FROM MessagesFts WHERE docid=1 AND
> Message MATCH 'ABC*';
> 0|0|TABLE MessagesFts VIRTUAL TABLE INDEX 1:
The problem is that there is no code in FTS3 that can evaluate
a MATCH expression without querying the full-text index (one could
write such code, but nobody has yet). So the plan that 3.6.21
produced for this query actually didn't work:
SQLite version 3.6.21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE VIRTUAL TABLE t1 USING fts3(x);
sqlite> INSERT INTO t1 VALUES('abc def');
sqlite> SELECT * FROM t1 WHERE docid = 1;
abc def
sqlite> SELECT * FROM t1 WHERE t1 MATCH 'abc';
abc def
sqlite> SELECT * FROM t1 WHERE docid = 1 AND t1 MATCH 'abc';
Error: SQL logic error or missing database
> Using 3.6.22, when MessagesFts contains a lot of entries that would
> match
> ABC*, the query takes exceedingly long time to run, since it seems
> to be
> doing the fts before narrowing down by docid. I imagine that it's
> supposed
> to narrow down by docid first.
That's true. And your proposal would improve performance for this kind
of query. Because of the way FTS3 works internally, it's not quite as
easy to implement as it should be though.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
