2017-08-24 14:38 GMT+02:00 Clemens Ladisch <[email protected]>: > Cecil Westerhof wrote: > > 2017-08-24 11:29 GMT+02:00 Clemens Ladisch <[email protected]>: > >> Cecil Westerhof wrote: > >>> But when I leave the ORDER BY out in this case, the result is the > same, but > >>> it looks like it is a bit faster. > >> > >> Are you sure? How does the output of EXPLAIN QUERY PLAN look like? > > > > No, I am not sure. I executed both a few times in the database browser. > > Both have widely varying used times. But at first glance it looked that > > without ORDER BY is about 2 times faster. But it is only at first glance, > > so certainly not something concrete. > > > > The explains. > > But I asked for EXPLAIN QUERY PLAN instead. :) >
Oops, mea culpa. EXPLAIN QUERY PLAN SELECT used, COUNT(used) FROM usedProverbs GROUP BY used ORDER BY used; 0|0|0|SCAN TABLE proverbs USING INDEX proverbs_used_idx EXPLAIN QUERY PLAN SELECT used, COUNT(used) FROM usedProverbs GROUP BY used; 0|0|0|SCAN TABLE proverbs USING INDEX proverbs_used_idx > > I do not really see a difference. > > The only difference is a Noop. > Not important I think, but I like to know ‘everything’: why is there an extra Noop. > > I also do not see a sort. > > Because it reads everything from an index that already is in the correct > order. > 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. -- Cecil Westerhof _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
