I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year.
The table structure looks like:
id | year | amount
The query I'm currently executing is:
SELECT year, count(year) FROM mytable GROUP BY year
This works just fine and gives me the results I expect.
The issue is that this these queries are being done as part of a web application and they currently take about 6-7 seconds to execute. I have an index on the year
field (and on amount
). I don't think I can pre-compute summary statistics because the query can have arbitrary where clauses added to it, e.g.
SELECT year, count(year) FROM mytable WHERE amount > 500 GROUP BY year
Though I could pre-compute the query in the case when there are no additional WHERE
clauses.
Is there anything I can do to speed up the computation of the count
part of the query? That seems to be where the slowdown is.
COUNT(*)
yields similar results. The create table statement isCREATE TABLE mytable(id int NOT NULL AUTO_INCREMENT,Year int NOT NULL,Amount DOUBLE NOT NULL, PRIMARY KEY(id));
– Jeff Storey Jan 26 '14 at 19:35(year, amount)
. It should speed up the query withoutWHERE
and probably the other one you have showed us. – ypercubeᵀᴹ Jan 26 '14 at 22:48