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
