On Mon, Sep 2, 2013 at 10:58 AM, Bert Huijben <rhuij...@apache.org> wrote:
> We anticipate that the wc_id column will be used more in future versions > and > I had hoped that the assumption that a better index match (matching more > usable columns) would always be preferable over one that uses less columns. > > All indexes have the wc_id column first, so I don't see a good reason that > Sqlite in this case prefers only this column of the specific (bad) index > over any other index that can help with the rest of the query. > Thank you; I have your complaint. I will try to come up with some new mechanism to solve your issues without the use of ANALYZE. Please recognize that there do exists real-world databases where it is more efficient to use a single lookup on a single column of an index rather than doing multiple lookups on the same index using multiple columns, which is the choice that SQLite faces in your scenario. It is not clear to me how SQLite is suppose to tell one case from the other without access to ANALYZE-like meta-information that describes the shape of the database content. But I will investigate and see what I can come up with. Please also recognize that if I change SQLite to work better with your data shape, that will also likely make it slower on some other application where the data has a very different shape, for example where the left-most index column is distinctive but the second column is not. The question becomes whether or not this will be a worthwhile tradeoff. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
