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? Cheers, Peter. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
