I’m in the early stages of developing an application that basically records web site statistics / analytics for reporting. I am required to have up to the second reporting for these numbers. I have multiple columns with plenty of information, making group by & order by ridiculous when using InnoDB on 500,000+ rows of data.
My current plan is to split the data in the following way: 1 table for all information which is InnoDB this is for proper accounting, 4 tables with summary information. When a visitor comes to the website 5 inserts will happen, 1 to main, 4 to summaries. When the webmaster/site owner comes to view his statistics it hits the summary tables and shows instantaneous data!
What I’d like to do is store all this summary information in a MEMORY-type table (from 10-200 rows each) I have been experimenting and this is working quite well on a small scale. 250kb each memory table so 1MB per website. I’m worried about server crash/ mysql restart situations that will clear these summary tables.
Is there a good way to flush these heap tables to disk, or just create a cron to do it manually…?
Any ideas or suggestions here would be great, as I said I’m in the beginning stages, so if I’m on the wrong track let me know!
EDIT: btw, my solution needs to be rather scalable - probably have 1000+ websites doing millions of hits per day.