On Fri, Jul 30, 2010 at 4:39 PM, Cory Nelson <phro...@gmail.com> wrote:
> 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. > I don't know where you saw that either. If you discover it someplace in the official documentation, please let me know so that I can remove it. Thanks. > > -- > Cory Nelson > http://int64.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- --------------------- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
