2

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.

|improve this question
  • You should have a combined index on (amount,year) for those WHERE queries. – Mihai Jan 26 '14 at 18:50
  • Right, but in the case where there is no where clause, it's still 6-7 seconds. – Jeff Storey Jan 26 '14 at 18:51
  • How big is the table? What Engine are you using? Please provide SHOW CREATE TABLE. If year is NOT NULL, you could use COUNT(*) instead of COUNT(year). – Rick James Jan 26 '14 at 19:11
  • The table has 30 million rows. Engine is InnoDB. Using COUNT(*) yields similar results. The create table statement is CREATE 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
  • 1
    I would add an index on (year, amount). It should speed up the query without WHERE and probably the other one you have showed us. – ypercubeᵀᴹ Jan 26 '14 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.