On 2/2/17, Hick Gunter <h...@scigames.at> wrote: > DISTINCT forces the query optimizer to create an intermediate table to hold > the results and compare each row of the non-distinct result set with an > automatically created index. It may also affect the query plan in a way that > chooses inefficient indices, which is more likely if you have not run > ANALYZE on the fully loaded database.
Creating an intermediate table is one way in which DISTINCT might be implemented. The query planner might also try to force the output into sorted order, so that it can eliminate duplicates simply by comparing against the previous output. The second mechanism can cause the use of inefficient indexes, if they are present and un-ANALYZED. The query planner computes an estimated run-time for each of various techniques it considers, and picks the one it thinks will run the fastest. Running ANALYZE helps the query planner to generate better (more accurate) cost estimates. I have not yet analyzed this situation sufficiently to tell what is going on. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
