Any update on this? It's seriously affecting performance in our application.
Best regards, Filip Navara On Tue, Aug 12, 2008 at 6:33 PM, Scott Hess <sh...@google.com> wrote: > [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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
