dbSendQuery(con, "CREATE INDEX year ON yrpc (year)")
dbSendQuery(con, "CREATE INDEX reporter_iso ON yrpc (reporter_iso)")
would sqlite use these for the query given?
it seems they wouldn't help with grouping, and the grouping seems antagonistic to filtering. if you're trying for performance, you can avoid having the query engine refer back to the original table during processing by using a covering index that includes the data fields in addition to the query fields
"CREATE INDEX covering (reporter_iso, year, trade_value_usd_exp, trade_value_usd_imp)"
or maybe even something like
"CREATE INDEX covering (reporter_iso, year, reporter_iso, trade_value_usd_exp, trade_value_usd_imp)"
Though I'm not sure how it's query planner would do with a duplicate column in the index.
It was an offhand. I was thinking to keep them in the same order as the group-by while still keeping the field in front for the initial filter, but given a moments thought I doubt it would make any difference, besides wasting some space in the index.
Comments
would sqlite use these for the query given?
it seems they wouldn't help with grouping, and the grouping seems antagonistic to filtering. if you're trying for performance, you can avoid having the query engine refer back to the original table during processing by using a covering index that includes the data fields in addition to the query fields
"CREATE INDEX covering (reporter_iso, year, trade_value_usd_exp, trade_value_usd_imp)"
or maybe even something like
"CREATE INDEX covering (reporter_iso, year, reporter_iso, trade_value_usd_exp, trade_value_usd_imp)"
Though I'm not sure how it's query planner would do with a duplicate column in the index.
The same field, twice, in the same index is some next level thinking. Where would you come across this kind of technique?
It was an offhand. I was thinking to keep them in the same order as the group-by while still keeping the field in front for the initial filter, but given a moments thought I doubt it would make any difference, besides wasting some space in the index.