> 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;
I think the point is that you cannot declare the ID field of general_text as an primary key. In FTS 3 tables, the docid member is already a primary key, so it is highly possible that your join does not use an index and parses all the rows of the table. A solution would be to insure that general.id and general_text.docid have the same id, which can probably be achieved by using last_insert_rowid() after inserting to general_text (warning, there were a bug in that case if you happen to delete and insert rows again in general_text - you'd need to do that within a transaction to have the correct rowid). Alex. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
