On Thu, Apr 08, 2010 at 11:52:13PM +0400, Alexey Pechnikov scratched on the wall: > > 1. With ordinary tables - fast
> 2. With ordinary tables and virtual table - very slow > > select role_exist.rowid as rowid, role.* from role_exist, role, role_fts > where role_exist.rowid=role.rowid and role_exist.rowid=role_fts.rowid; > ^CCPU Time: user 34.058128 sys 1.720108 > Error: interrupted Is there a point you're trying to make, or a question you're asking? All you've done is posted two times that aren't that unexpected. Virtual Tables are completely abstract to the query planner. The only way to do any type of indexed or high efficiency lookup is if the virtual table allows that to happen. Otherwise, there is *nothing* the query planner can do, except perform a full table scan -- even on the ROWID column. The issue isn't with the SQLite core, it isn't with the query planner-- it is with the virtual table module you're using. Most modules are designed to be used in specific ways. If you're using it in some other way, it isn't a surprise it doesn't work very well. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
