The following query returns about 3000 rows with the date and number of people who enrolled each day:
select date(registerDate) as date, count(*) as total from users group by date limit 5000;
I currently use another query that uses UNIONS to count totals from several different tables. Many of these tables have become quite large, some having more than one million rows, which is slowing things down. So my idea is to create a new statistics table. I will create a cron job that runs at 1AM and it will count the number of new users, searches, favorites from the pervious day and insert them into the statistics table.
This will work going forward, but I need a way to take the data from the past and insert it into this new table. Is it possible to take my query above and combine it with an INSERT query? Does this make sense?
Ok, I made some progress. Tinkering around I came up with this query that did what I wanted:
INSERT INTO statistics (date, users)
(select date(registerDate) as date, count(*) as total from users group by date);
As I was about to change the query and run it again for the searches table I figured I would run into trouble because I might end up with two rows for ‘2008-02-01’, as an example. How can I tweak this query so the insert will add the total to the searches column if that date already exists? And if that particular date doesn’t exist it will insert a new row altogether?
use the ON DUPLICATE KEY UPDATE option of the INSERT statement
I did some further digging around and came up with the following that works. Thanks for pointing me in the right direction:
INSERT INTO statistics ( date, logins )
select s.tempDate, ct
( SELECT t.tempDate, count(*) as ct
FROM logins t
GROUP BY t.tempDate
) as s
ON DUPLICATE KEY UPDATE statistics.logins = ct;