Hi all, Doing a join on a fts3 table can be very slow. I'm using these tables:
CREATE TABLE general ( ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ... ); CREATE VIRTUAL TABLE general_text using fts3 ( ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, a TEXT, b TEXT, c TEXT, d TEXT, e TEXT DEFAULT '', f TEXT ); which creates these tables CREATE TABLE general_text_content( docid INTEGER PRIMARY KEY,c0ID, c1a, c2b, c3c, c4d, c5e, c6f); CREATE TABLE general_text_segdir( level integer, idx integer, start_block integer, leaves_end_block integer, end_block integer, root blob, primary key(level, idx)); CREATE TABLE general_text_segments( blockid INTEGER PRIMARY KEY, block blob); and fill them with about half a million rows. On these tables this query is very slow (about 1 row per second) select g.id from general g, general_text gt where g.id = gt.id; and these ones have a normal speed: select g.id from general g, general_text_content gt where g.id = gt.docid; select g.id from general g, general_text_content gt where g.id = gt.c0ID; What is causing the fts3 code to make joining so slow? Cheers, Jos _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users