I need to calculate the num of rows created on a daily basis for a huge Table in mysql. I'm currently using
我需要计算在mysql中为一个巨大的表每天创建的行数。我正在使用
select count(1) from table_name group by Date
THe query is taking more 2000sec and counting. I was wondering if there's any optimized query or a way to optimize my query.
查询将花费2000秒并计数。我想知道是否有任何优化的查询或优化查询的方法。
3 个解决方案
#1
1
If you're only interested in items that were created on those dates, you could calculate the count at end-of-day and store it another table.
如果您只对在这些日期上创建的项感兴趣，您可以在结束时计算计数并将其存储到另一个表中。
That lets you run the COUNT query on a much smaller data set (Use WHERE DATE(NOW()) = Date and drop the GROUP BY)
这使您可以在更小的数据集上运行COUNT查询(使用WHERE DATE(NOW()) = DATE并删除组BY)
Then then query the new table when you need the data.
然后在需要数据时查询新表。
#2
1
- Make sure that "date" field is of "date" type, not datetime nor timestamp
- 确保“date”字段是“date”类型，而不是datetime或timestamp
- Index that column
- 索引列
- If you need it for one day, add a "where" statement. i.e. WHERE date="2013-07-10"
- 如果你需要一天，可以加上“where”。例如,日期= " 2013-07-10 "
#3
0
Add an index on the Date column, there's no other way to optimize this query that I can think of.
在日期列上添加索引，没有其他方法可以优化我所能想到的查询。
CREATE INDEX ix_date
ON table_name (Date);
#1
1
If you're only interested in items that were created on those dates, you could calculate the count at end-of-day and store it another table.
如果您只对在这些日期上创建的项感兴趣，您可以在结束时计算计数并将其存储到另一个表中。
That lets you run the COUNT query on a much smaller data set (Use WHERE DATE(NOW()) = Date and drop the GROUP BY)
这使您可以在更小的数据集上运行COUNT查询(使用WHERE DATE(NOW()) = DATE并删除组BY)
Then then query the new table when you need the data.
然后在需要数据时查询新表。
#2
1
- Make sure that "date" field is of "date" type, not datetime nor timestamp
- 确保“date”字段是“date”类型，而不是datetime或timestamp
- Index that column
- 索引列
- If you need it for one day, add a "where" statement. i.e. WHERE date="2013-07-10"
- 如果你需要一天，可以加上“where”。例如,日期= " 2013-07-10 "
#3
0
Add an index on the Date column, there's no other way to optimize this query that I can think of.
在日期列上添加索引，没有其他方法可以优化我所能想到的查询。
CREATE INDEX ix_date
ON table_name (Date);