On Thu, Jul 29, 2010 at 5:25 PM, <peter.hiza...@parc.com> wrote: > Hello, > > Consider two tables: > >> create table a(num integer primary key); >> create table b(num integer primary key); > > After loading both tables we have 1000000 rows in table a and 1000 in table > b. We make sure b.num is a subset of a.num. Now we execute: > >> analyze > > We run two queries with timer on: > >> select count(*) from a join b using(num); >> select count(*) from b join a using(num); > > Both queries return 1000, the 'a join b' takes 0.5 and 'b join a' 0.0. SQLite > planner does not reorder join in first query to take advantage of smaller > cardinality of b. The reason seems to be that there is no statistics > collected for num column index, which is an alias for rowid b-tree. After > adding explicit indexes on a(num) and b(num) and analyzing the database both > queries take 0.0. > > Why SQLite does not maintain statistics for rowid indexes?
I can't remember where I saw it, but such optimizations were left out of SQLite intentionally -- it's expected that the programmer should optimize things. -- Cory Nelson http://int64.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
