Hi Ryan Nobody is proposing that QP should automagically add an index, I'm only asking why the QP does not use already added index, that is specially added for this specific case. I don't thinks this is a very "obscurest of use-case" or to much to ask for, in fact, this is the expected behavior for even the simplest SQL engines, and so especially sqlite.
Fredrik On Fri, Sep 20, 2019 at 2:36 PM R Smith <ryansmit...@gmail.com> wrote: > > On 2019/09/20 11:12 AM, Dominique Devienne wrote: > > > > But who says the GROUP BY must return rows in ASCending order? > > > > A lot of us "oldies" of this ML well know the order is arbitrary and > > subject to change w/o an explicit ORDER BY. > > So the GROUP BY is allowed, AFAIK, to return rows in DESCending order > just > > the same. And to do so efficiently > > as Fredrik points out, since indexes (or indices, I never know) work > > I had a post on here, about 7 years ago explaining how the correct > English is indeed "Indices", same as Matrix --> Matrices and Vortex --> > Vortices etc. It was pointed out to me (correctly) that while we hold > that use in describing the Index multiples on books or catalogues or > such, when specifically talking about a Database, and because of naming > rigidity in SQL language, it is perfectly acceptable and even preferred > to use "Indexes" as the plural for "Index". I do it too now, like all > teh cool kids. :) > > An area of contention is that it hides the value of the word "Indexes" > when used as a verb - as in > "What does he do here?" > "He indexes the cards". > Or present "Mary indexes the books" similar in meaning to present > continuous "Mary is indexing the books". > > But, that whole whinge is irrelevant, it's very clear from context which > "Indexes" is meant. > > The American-English Merriam-Webster seems quite more ok with this than > some older English dictionaries, but it has evolved now and I do not > hear anyone anymore insisting on "Indices". My prediction is that this > will also not remain a "Database-Only" shift. I'm already hearing people > refer to "book Indexes". > > > > equally well in both directions. In fact, it could > > return rows in arbitrary / random order too! > > > > The query-planner does see the ORDER BY that follows the GROUP BY after > > all, so it could well decide > > to group in DESCending order, thus avoiding the ordering completely, like > > it already does for ASCending. > > This would be a great optimisation, and from 30,000ft, it does indeed > seem > > like a "simple" one compared > > to all the advanced optimisations already implements, as Fredrik > mentioned. > > > > I might even say that it looks like a "low-hanging-fruit", if I dared :). > > Nothing wrong with your statement or assessment. I don't know how much > effort or code addition this would require, I'm guessing quite little - > but I would like to say that this is the very obscurest of use-cases > reported, and if it does require any increase in CPU cycles for the > normal case (which is how my stuff work) - I don't want it. > > To be clear, the question (paraphrasing and shortening horribly) is > something like this: > > The QP will automagically use an Index on Group By - > Which is nice as a side-benefit I don't have to explicit an index, which > saves my DB some space/effort, > But now I also want to use the side-benefit for reverse ordering, and it > doesn't work > (But it works perfectly well if I DO explicitly declare the Index). > > Now it seems fine to suggest the optimization, but the problem is that > this requires possibly a lot of effort and (hopefully very little) > additional code, to facilitate a shortcut upon a shortcut for a thing > that is completely QP-specific, open to change in future if a better > grouping algorithm presents itself, and not within hard documented > design methodology. > It's probably not as smart an optimization as it would seem, and the > advice should always remain: USE AND INDEX when you want stuff indexed > and ordered. Don't rely on QP quirks for your business logic or speed of > execution. > > In other words, EVEN if the devs do add the quirk (perhaps it's really > trivial), I would still say don't depend on it. Some future update may > find a better way to group and change this method, and that day might > slow down your code. > > > Cheers, > Ryan > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
