[Full text of an example later in the email.] For a query like this:
SELECT * FROM t_ft WHERE docid = (SELECT rowid FROM t WHERE label = 'y'); fulltextBestIndex() gets a SQLITE_INDEX_CONSTRAINT_EQ on the docid column, which can be efficiently accessed, so fulltextFilter() can do a very efficient query. Meanwhile, for this: SELECT * FROM t_ft WHERE docid IN (SELECT rowid FROM t WHERE label = 'y'); fulltestBestIndex() gets no constraints, and thus can't do anything, so you end up with a full table scan of the fts3 table. As far as I can tell from sqlite/src/where.c, the IN operator is explicitly not optimized for virtual tables. I can't quite figure out why, though. An oversight? It makes things very inefficient for certain schema arrangements. You can work around it by making your sub-select explicit in your application code, but since IN is optimized fine for regular tables, it's a bit unexpected. Thanks, scott An example for feeding to sqlite3 (though you'll need to set breakpoints in fts3.c to see it happening): DROP TABLE IF EXISTS t; DROP TABLE IF EXISTS t_ft; CREATE TABLE t ( id INTEGER PRIMARY KEY, label TEXT ); CREATE INDEX t_idx ON t(label); CREATE VIRTUAL TABLE t_ft USING fts3(c); INSERT INTO t (id, label) VALUES (null, 'x'); INSERT INTO t_ft (docid, c) VALUES (LAST_INSERT_ROWID(), 'This is a test'); INSERT INTO t (id, label) VALUES (null, 'y'); INSERT INTO t_ft (docid, c) VALUES (LAST_INSERT_ROWID(), 'That was a test'); INSERT INTO t (id, label) VALUES (null, 'x'); INSERT INTO t_ft (docid, c) VALUES (LAST_INSERT_ROWID(), 'Another test'); INSERT INTO t (id, label) VALUES (null, 'z'); INSERT INTO t_ft (docid, c) VALUES (LAST_INSERT_ROWID(), 'And another'); SELECT * FROM t_ft WHERE docid = (SELECT rowid FROM t WHERE label = 'y'); -- Efficient SELECT * FROM t_ft WHERE docid IN (SELECT rowid FROM t WHERE label = 'y'); -- Inefficient, sub-select matches one row SELECT * FROM t_ft WHERE docid IN (SELECT rowid FROM t WHERE label = 'x'); -- Inefficient, sub-select matches multiple rows _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
