Cecil Westerhof wrote: > 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
> why is there an extra Noop. When the query planner sees the ORDER BY, it prepares a temporary index to do the sorting. When it can later prove that the sorting is not actually needed, it deactivates that instruction by overwriting it with the Noop. > 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? The GROUP BY must compute a sum for each distinct "used" value. When it reads those values in order, it can compute a single sum at a time and does not need to keep temporary results. So it prefers to read from an index, too. Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
