On Sat, Dec 7, 2019 at 3:50 AM Simon Slavin <slav...@bigfraud.org> wrote:
> On 7 Dec 2019, at 2:26am, Shawn Wagner <shawnw.mob...@gmail.com> wrote: > > > The first one uses the index for all sorting, but the second one only > uses it for sorting a, not b. I feel like the descending sort could make > use of the index too, just reading the b sections backwards to get the > right order. Is there something I'm overlooking that would make this sort > of optimization impractical or otherwise a bad idea? > > Hmm. Try running ANALYZE and then doing the EXPLAIN QUERY PLAN lines > again. > > But I think that without 'chunkiness' information (how many values columns > a and b have) it would not be worth doing the complicated programming > required for reverse-mini-scanning of that index. The programming is quite > complicated and unless your index "b" is chunky it won't save you much time > over the plan shown. > So it's better to allocate memory, block the execution until all rows are read and use cpu time to do unnecessary sorting that could easily be avoided by just reading index backwards? Is it really so hard to program it? I do not think so. However the heuristic to decide when to do backward index scan needs to be smart enough to select this only as last resort optimization, just before falling back to explicit sort. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
