Optimizing aggregation queries

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Optimizing aggregation queries

wmertens
Given a logging table:

CREATE TABLE log(type TEXT, amount INTEGER)
SELECT type, SUM(amount), COUNT(*) FROM log GROUP BY type

What would be good approaches to make the query fast?

Wout.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Optimizing aggregation queries

Simon Slavin-3
On 23 Nov 2018, at 3:44pm, Wout Mertens <[hidden email]> wrote:

> CREATE TABLE log(type TEXT, amount INTEGER)
> SELECT type, SUM(amount), COUNT(*) FROM log GROUP BY type
>
> What would be good approaches to make the query fast?

Create an index for "log(type, amount)".

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users