On Fri, Nov 15, 2013 at 8:02 AM, Dominique Devienne <ddevie...@gmail.com>wrote:
> > What I wonder though is how many is "too many distinct values in the > left-most columns", i.e. is 50 too many or not. > > Will there be a pragma for the cut-off number, or there's not really a > cut-off number but instead the true cardinality of the left-most columns is > evaluated and has bearings on the plan cost and it "wins" only if no other > plans are cheaper. Richard? > The current decision algorithm will only consider a skip-scan if there is an average of at least 50 occurrences of each distinct value in the index. So it does not matter how many distinct values there are, only how many distinct values there are relative to the total number of rows in the table. Once the average-repeat-count>50 criteria is met, then the estimated cost of running skip-scan is compared against the estimated cost of other query plans (full table scan, various other indexed lookups, constructing a transient index, etc.) and the plan with the lowest cost wins. I emphasize that this is the *current* decision algorithm. No doubt it will be tuned for better performance as the code evolves. Note that SQLite cannot know that there is an average of 50 or more occurrences of each distinct value in the index unless you have run ANALYZE. Hence, the skip-scan algorithm will only be used after ANALYZE has been run. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
