Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You should have a combined index on (amount,year) for those WHERE queries. –  Mihai Jan 26 at 18:50
    
Right, but in the case where there is no where clause, it's still 6-7 seconds. –  Jeff Storey Jan 26 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 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 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 at 22:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.