When I can I'll try with the latest release. Thanks for all your help.
On Tue, Jul 8, 2014 at 4:05 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 06/25/2014 04:44 PM, João Ramos wrote: > >> The sample column has some sensitive data that I can't share, so I'll have >> to change it and then try to reproduce the problem (I removed it before >> because I assumed that it wouldn't be important). >> >> On Mon, Jun 23, 2014 at 3:26 PM, Dan Kennedy <danielk1...@gmail.com> >> wrote: >> >> On 06/23/2014 05:48 AM, João Ramos wrote: >>> >>> Here you go: >>>> >>>> sqlite_stat1 (before - good planning) >>>> HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5 >>>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2 >>>> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14992 2999 2 >>>> >>>> sqlite_stat1 (after - bad planning) >>>> HistoryEntry idx_HistoryEntry_uri_historyOrder 15492 45 5 >>>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 15492 5 2 >>>> HistoryEntry idx_HistoryEntry_sourceType_sourceId 15492 3099 2 >>>> >>>> > The issue, it turned out, is in the sqlite_stat1 data. The first set of > stat1 data above suggests that using index "idx_HistoryEntry_uri_historyOrder" > for a (uri=? AND historyOrder=?) lookup returns 5 rows. But using > "idx_HistoryEntry_historyOrder_historyTimestamp" for a (historyOrder=?) > lookup returns 6 rows. So in this case SQLite prefers the former. > > But using the second set of sqlite_stat1 data, both lookups appear to > return 5 rows. Both strategies are considered equally good. So SQLite just > picks either. In this case, clearly it's making an unlucky choice. > > Version 3.8.5 includes logic to prefer the (uri=? AND historyOrder=?) > lookup over the (historyOrder=?) one on the basis that it uses a superset > of the WHERE clause terms. Introduced here: > > http://www.sqlite.org/src/info/683dd379a293b2f3 > > So upgrading to 3.8.5 might fix the problem. > > Dan. > > > > > _______________________________________________ > 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
