On Fri, Apr 09, 2010 at 12:50:56AM +0400, Alexey Pechnikov scratched on the wall: > Hello! > > May be is needed to show this more informatively. The record > "TABLE role_fts VIRTUAL TABLE INDEX 0:" is not useful I think.
http://sqlite.org/fts3.html 1.3 Simple FTS3 Queries [...] FTS3 tables can be queried efficiently using SELECT statements of two different forms: - Query by rowid [...] the form "rowid = ?" [...] - Full Text query [...] MATCH [...] If neither of the two query strategies enumerated above can be used, all queries on FTS3 tables are implemented using a linear scan of the entire table. Although you've got rowid= type queries, they're JOINs, not searches for specific values, so the first point doesn't really count. I suppose there is some chance the query planner is assuming any column named "rowid" is a cheap lookup, and somehow favoring the FTS virtual table in the JOIN sequence, but that seems like a bit of a long-shot. > And is not helpful than planner using full table scan without any > alert. Exactly like every other query the database makes? Why would this be different? > So vurtual tables are similar to regular tables but we have a lot of > hidden planner problems. Virtual tables are *not* similar to regular tables. They're highly specialized, and each type of virtual table is specialized in a different manner. If you treat them like normal tables you're going to have lots of problems. If you want something that looks and acts like a normal table... use a normal table. -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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
