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
