I have always parsed logs to get views for particular pages, however I am wondering if there is a good design for storing data in a MySQL table for later use in analytics?
My thought is to have one table which has optimal performance for write actions (logging) and another table (or set of tables) to query against for analytic purposes. Ideally the second set would have indexes.
In particular, does have any suggestions for the following?
- Best engine for frequent writes (for logging obviously)?
- Is my assumption of having one table which has no indexes (for faster inserts) then another table (with indexes for queries) even a good design?
- If point two is a good design, what is the best mechanism for migrating records? I assume that replication here would not really work if one table has indexes and another does not…but I am not a pro when it comes to replication.
I have many more questions to follow up these, however these three are my main questions after combing the Internet (and this site) for a few days.