Re: [sqlite] Order of columns in group by statement affects query performance

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

Re: [sqlite] Order of columns in group by statement affects query performance

Do you have any experience with SQLite virtual tables? I guess not. There are 20 issues here: 1) The abstract problem of choosing an Index for optimizing GROUP BY 2) the SQLite implementation (which I was referring to) Ad 1) Any index that covers all the GROUP BY fields is a "good" index

Re: [sqlite] Order of columns in group by statement affects query performance

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 >

Re: [sqlite] Order of columns in group by statement affects query performance

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

Re: [sqlite] Order of columns in group by statement affects query performance

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

Re: [sqlite] Order of columns in group by statement affects query performance

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

Re: [sqlite] Order of columns in group by statement affects query performance

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

Re: [sqlite] Order of columns in group by statement affects query performance

AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints in the virtual table description). If you have an index that covers the GROUP BY clause in field order, then aggregate functions need store only the current value; if not, then you need an ephemeral table to hold the

Re: [sqlite] Order of 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 particular order. SQLite could, in > >

Re: [sqlite] Order of columns in group by statement affects query performance

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

Re: [sqlite] Order of columns in group by statement affects query performance

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,

Re: [sqlite] Order of columns in group by statement affects query performance

*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

[sqlite] Order of columns in group by statement affects query performance

Hello, I am using sqlite3 (3.7.15.2 at the moment) in a project. I discovered that the order of columns in a group by affects the performance of a query. Is this expected? For example: Table: Column A int, Column B int, Column C int One Index: A,B (combined) Query 1: SELECT A,B,count(*)