CODEWITHSUNDEEP
mysqlsqldatetime
amacks
amacks

Reputation: 80

Optimizing "Count less than" query

I'm working on a MySQL query that works, but is painfully slow, even with only ~11,000 rows.

   describe select date(date_added) as curr_date, 
     (select count(*) from calendar_entries as ce where date(date_added)<=curr_date ) as until_count
   from
   calendar_entries
   group by date(date_added)

The goal is to measure table growth over time, so for each day where the table was changed, give me the total number of lines at that time. It could be done with some sort of sum, i.e. (pseudocode)

Total(n) = Total(n-1)+numberAdded(n)

but I'm not sure how to go about that in SQL

A

Upvotes: 2

Views: 42

Answers (1)

O. Jones
O. Jones

Reputation: 108796

OK, first you need to know the dates on which the table was changed.

       SELECT DISTINCT DATE(date_added) active_date FROM calendar_entries

Then you need to figure out how to count up the entries on or before each of those dates.

SELECT COUNT(*) rows_added, b.active_date on_or_before
  FROM calendar_entries a
  JOIN (
           SELECT DISTINCT DATE(date_added) active_date FROM calendar_entries
       ) b ON a.date_added < b.active_date + INTERVAL 1 DAY

Fast? Maybe not.

Notice that a_timestamp < a_date + INTERVAL 1 DAY captures all the timestamps during or before the date.

Upvotes: 1

Related Questions