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
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.