This question already has an answer here:
这个问题已经有了答案:
- Speeding up row counting in MySQL 5 answers
- 在MySQL 5中加快行计数
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
我有表包含3亿记录，有两个字段“name”和“date”。现在我需要统计上个月的记录，所以我做了
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.
但是这需要很长时间，所以我需要建议如何优化计数查询以及如何获得快速的结果。
1 个解决方案
#1
4
@Valex has the right solution, which is an index on the date field.
@Valex有正确的解决方案，即日期字段的索引。
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.
我想说的是，一个包含3亿行并不断增长的表是一个大表。这个查询表明，最近的数据比旧的数据更重要。如果是的话，您应该学习一下分区。
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.
分区是将大表分割成较小的存储单元的一种方法。如果一个查询只需要访问其中的一个，那么只需读取数据进行处理——这将大大节省成本。例如，如果您的数据按月进行分区，并且您有36个月的数据，那么您的查询将只需要访问2个月的数据。这将节省阅读所有数据的95%。
Here is the MySQL documentation on partitioning.
下面是关于分区的MySQL文档。
#1
4
@Valex has the right solution, which is an index on the date field.
@Valex有正确的解决方案，即日期字段的索引。
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.
我想说的是，一个包含3亿行并不断增长的表是一个大表。这个查询表明，最近的数据比旧的数据更重要。如果是的话，您应该学习一下分区。
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.
分区是将大表分割成较小的存储单元的一种方法。如果一个查询只需要访问其中的一个，那么只需读取数据进行处理——这将大大节省成本。例如，如果您的数据按月进行分区，并且您有36个月的数据，那么您的查询将只需要访问2个月的数据。这将节省阅读所有数据的95%。
Here is the MySQL documentation on partitioning.
下面是关于分区的MySQL文档。