Selectivity is known -- since it's a primary key, which is unique -- it will be 1. Cardinality can vary.
Peter. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kees Nuyt Sent: Friday, July 30, 2010 11:58 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Statistics on integer primary key On Thu, 29 Jul 2010 17:25:50 PDT, <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? Because the cardinality is known, given that rowid is unique by definition? -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
