On Fri, 26 Apr 2013 11:12:15 +0200
Hick Gunter wrote:
> It is not unreasonable to assume that in a well designed SQL
> Statement the GROUP BY clause will be backed up by the necessary
> index and an identical ORDER BY clause
That is an entirely unreasonable assumption. Order may or not matter.
BY** clauses of the query, but also from any ON
or USING clauses if the query is a join.
-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Donnerstag, 25. April 2013 16:34
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Order of columns in group
On 25 Apr 2013, at 4:23pm, Jay A. Kreibich wrote:
> Except there is no such thing as "GROUP BY order". SQL Golden Rule:
> If there is no ORDER BY, the rows have no order. According to SQL,
> neither the groups, nor the rows within a group (as they are fed into
> aggregates) have a defined
On Thu, Apr 25, 2013 at 05:08:04PM +0200, Daniel Winter scratched on the wall:
> 2013/4/25 James K. Lowden
>
> >
> > Nonsense. The query parser sees GROUP BY A,B. The optimizer sees an
> > index ordered B,A. By permuting the order of the columns in the GROUP
> > BY clause, it finds a match for
On Thu, Apr 25, 2013 at 10:29:34AM +0200, Hick Gunter scratched on the wall:
> AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints
> in the virtual table description).
They're not the same clause, they don't do the same thing.
Now, it is true that most database systems imp
2013/4/25 James K. Lowden
>
> Nonsense. The query parser sees GROUP BY A,B. The optimizer sees an
> index ordered B,A. By permuting the order of the columns in the GROUP
> BY clause, it finds a match for the index and uses it.
>
> Yes, the problem is O(n^2), where n is the number of columns in
On Thu, 25 Apr 2013 10:29:34 +0200
Hick Gunter wrote:
> AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from
> hints in the virtual table description).
That might be so, in some limited sense. It's obviously false in
general because they mean different things and have different eff
columns in group by statement affects query
performance
On Wed, 24 Apr 2013 17:46:00 +0100
Simon Slavin wrote:
> On 24 Apr 2013, at 5:14pm, Igor Tandetnik wrote:
> > Note though that the query doesn't have an ORDER BY clause. It
> > doesn't request rows in any particula
On Wed, 24 Apr 2013 17:46:00 +0100
Simon Slavin wrote:
> On 24 Apr 2013, at 5:14pm, Igor Tandetnik wrote:
> > Note though that the query doesn't have an ORDER BY clause. It
> > doesn't request rows in any particular order. SQLite could, in
> > principle, reorder columns in GROUP BY to take advan
On 24 Apr 2013, at 5:14pm, Igor Tandetnik wrote:
> Note though that the query doesn't have an ORDER BY clause. It doesn't
> request rows in any particular order. SQLite could, in principle, reorder
> columns in GROUP BY to take advantage of the index. I suppose the optimizer
> just happens to
On 4/24/2013 11:49 AM, Larry Brasfield wrote:
*Daniel Winter wrote:0*
Table: Column A int, Column B int, Column C int
One Index: A,B (combined)
Query 1: SELECT A,B,count(*) from tableTest group by A,B
Query 2: SELECT A,B,count(*) from tableTest group by B,A
Query 1 will use the index, whi
*Daniel Winter wrote:0*
> I discovered that the order of columns in a group by affects the
> performance of a query. Is this expected?
Yes.
> For example:
>
> Table: Column A int, Column B int, Column C int
> One Index: A,B (combined)
>
> Query 1: SELECT A,B,count(*) from tableTest group by A
12 matches
Mail list logo
