Making an INSERT using a SELECT
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?