CODEWITHSUNDEEP
mysqlsqldatabase
Rishabh Agarwal
Rishabh Agarwal

Reputation: 101

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

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.

Upvotes: 1

Views: 867

Answers (3)

Barmar
Barmar

Reputation: 781096

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

Upvotes: 0

Jehad Keriaki
Jehad Keriaki

Reputation: 545

  • 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"

Upvotes: 1

ben
ben

Reputation: 1936

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.

Upvotes: 1

Related Questions