Calculate no of rows created on daily basis from a huge table in mysql

Question

I need to calculate the num of rows created on a daily basis for a huge Table in mysql. I'm currently using

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.

Have you looked over here? http://stackoverflow.com/questions/12479677/sql-server-count-is-slow — Nicholaus Chipping, Jul 10 '13 at 21:01
Do you really need to recalculate no of rows for past days every single time??? — peterm, Jul 10 '13 at 21:02
@peterm I just need the statistics for one time for now to see how many rows are added everyday to my table. — Rishabh Agarwal, Jul 10 '13 at 21:08
@Barmar by index on date column means I should add a between clause to limit to a range of date? — Rishabh Agarwal, Jul 10 '13 at 21:09
@RishabhAgarwal an index is a modification to the table creation logic. See: `ALTER TABLE` syntax. ie: `ALTER TABLE table_name ADD INDEX (Date);` — , Jul 10 '13 at 21:13
Oh thanks.... If I create an Index now on Date will it populate itself for all previous entries? — Rishabh Agarwal, Jul 10 '13 at 22:40

Jehad Keriaki · Answer 1 · 2013-07-10T21:10:04.343

1

Make sure that "date" field is of "date" type, not datetime nor timestamp
Index that column
If you need it for one day, add a "where" statement. i.e. WHERE date="2013-07-10"

edited Jul 10 '13 at 21:10

answered Jul 10 '13 at 21:04

Jehad Keriaki

score 1 · Answer 2 · answered Jul 10 '13 at 21:05

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)

Then then query the new table when you need the data.

Barmar · Answer 3 · 2013-07-10T21:19:29.477

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);

edited Jul 10 '13 at 21:19

answered Jul 10 '13 at 21:00

Barmar

by Index on date column u mean I should add some range of date using between or (<,>) logic clause ? – Rishabh Agarwal Jul 10 '13 at 21:10
No, it means you should create an index in the database. See the updated answer. – Barmar Jul 10 '13 at 21:20

3 Answers3