On Sat, Sep 25, 2010 at 6:35 PM, Richard Hipp <[email protected]> wrote:
> > You have a very unusual data distribution in your tables. SQLite does not > know this and so it chooses a query plan (in 3.7.1 and later) that assumes > a > different and more typical data distribution. The new query plan works > better for most cases, but is much worse in your unusual case. > > > Richard, I think it's something different. I took the base and here my observations: - I confirm this on windows, 80 seconds vs 400 milliseconds. - The only versions that does the query good is 3.6.20, 3.6.23, prior (3.16), and after (3.7.2) performs the query slowly (the same degradation level). - On "bad" versions the same query but with LEFT JOIN works fine. - For all good scenarios (inner join on 3.6.20, 3.6.23, left join on the same and others ) seems like sqlite chooses object_formats scanning and page_table lookup (the order of entries in explain query) "0" "0" "TABLE object_formats" "1" "1" "TABLE page_table WITH INDEX page_table_index" - For bad scenarios it reverses, full-scans with index and lookup with full-scan that gives actually 160x multiplication with slowness. "0" "1" "TABLE page_table WITH INDEX page_table_index" "1" "0" "TABLE object_formats" So my guess is that an optimization introduced in 3.6.20 and existed in .23 was removed in 3.7 for some unknown reasons. Maybe actually there was a reason, but the difference for INNER JOIN and LEFT JOIN leaves a chance it's some side effect. But I may be wrong Max _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
