Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

to:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hamish Allan Gesendet: Montag, 03. April 2017 16:24 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY Thanks, Hick. I now understand that it's undefined wh

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

index on (b, a) > > The former returns all groups of c with the top one being the one row > returned by the latter. > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Hamish Allan >> Sen

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

f the query > contains more than one min() and/or max() aggregate function. Only the > built-in min() and max() functions work this way." > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > Auftrag von Hamish All

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

is the purpose of the ORDER BY? >>>> >>>> The value of a used for the order by is from some random row in the >>>> grouping of c. Are there relationships between a, b, c, d that you have >>>> not >>>> documented nor told us about? >&g

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

being the one row returned by the latter. > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Hamish Allan > Sent: Monday, 3 April, 2017 02:51 > To: SQLite mailing list > Subject: Re: [sqlite] Indexing WHERE with

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

sers [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hamish Allan Gesendet: Montag, 03. April 2017 10:51 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY Ah. My purpose is to determine "d for t

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

t index is on (b, c). The order by is useless. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hamish Allan Sent: Sunday, 2 April, 2017 17:28 To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

ers-boun...@mailinglists.sqlite.org] On Behalf Of Hamish Allan Sent: Sunday, 2 April, 2017 17:28 To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER BY Given a table: CREATE TABLE x (a INT, b INT, c TEXT, d TEXT); the query: SELECT d FROM x WHERE b = 1 GR

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

seless. > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Hamish Allan >> Sent: Sunday, 2 April, 2017 17:28 >> To: sqlite-users@mailinglists.sqlite.org >> Subject: [sqlite] Indexing WHERE w

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

On 3 Apr 2017, at 12:27am, Hamish Allan wrote: > SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a; Your problem comes down to this: If you are GROUPing BY c, why do you want ORDER BY a ? If you remove the "ORDER BY a" clause then the ideal index would be on (b, c). But

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

. > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Hamish Allan > Sent: Sunday, 2 April, 2017 17:28 > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

I had nearly the same question a month ago (Subject: Index usefulness for GROUP BY). In my case, the best index was on the WHERE clause because it eliminated the scan and returned only the few important rows for the other clauses. However, the best result will depend on how many rows are

[sqlite] Indexing WHERE with GROUP BY and ORDER BY

Given a table: CREATE TABLE x (a INT, b INT, c TEXT, d TEXT); the query: SELECT d FROM x WHERE b = 1 GROUP BY c ORDER BY a; shows the following plan, without indexes: 0|0|0|SCAN TABLE x 0|0|0|USE TEMP B-TREE FOR GROUP BY 0|0|0|USE TEMP B-TREE FOR ORDER BY I can create an index to cover the