How to optimize count query on large table

Question

I have table contains 300 Million record with two field "name" and "date". now I need to count record of last month so I did

select count(*) from lenders_transaction WHERE dates >= now()-interval 1 month;

But it is taking long time , so I need advise How to optimize count query and how to get fast result.

There is no index on dates field . If i add then it would make query fast ? — J.Rob, Jul 26 '13 at 11:42
@valex . . . You should add that as an answer so others can upvote it. — Gordon Linoff, Jul 26 '13 at 11:45

score 4 · Answer 1 · answered Jul 26 '13 at 11:48

@Valex has the right solution, which is an index on the date field.

I want to comment that a table of 300 millions rows, and growing, is a large table. This query suggests that recent data is more important than older data. If so, you should be learning about partitioning.

Partitioning is a way of splitting a large table into smaller storage units. If a query only needs to access one of them, then only that data is read for processing -- a big savings. For instance, if your data were partitioned by month and you had 36 months of data, then your query would only have to access 2 months worth. This would be a savings of almost 95% over reading all the data.

Here is the MySQL documentation on partitioning.

How to optimize count query on large table

1 Answers1