On 2017/08/24 4:48 PM, Cecil Westerhof wrote:
Yes, and the EXPLAIN QUERY PLAN shows that. But that leads to my next question: With a scan the whole table is used. In the first situation I can understand that the index is used, because that saves the sort, which can be significant. But why is it used in the second case? To be clear: I do not want to be smart, I want to understand.
That's because the GROUP BY functions by grouping things together, and to look-up what group the next item should fall in with, the Query planner needs to look up that group's index in the grouped list, and for THAT it needs an Index. It was actually added as an optimization recently (I think) that if your order-by happens to use the same column structure as the group-by, then that same Index that was conjured up for grouping is also used for the ordering, so it saves a step.
If your order-by differs however, it will need to conjure up a whole new sorting-index mechanism just for that.
Hope that clears it up, Cheers! Ryan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
