MySQL table design of logging tables

Question

I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization.

Currently we have a table that logs session data, it writes about 1.2million rows a month. At the beginning of each month we split off last months rows and move them to a table called "tbl_session_mm_yyyy". We want to start querying the live table and the older tables and pull out details like last logged in (simple sql query, select maxdate from tbl_live, where accountid = 123 union select etc from tbl_old etc).

Now i'm thinking that the design of our database structure isn't quite right for this. So where we would have 12 tables for the year, each year, we would have to keep building huge unions to get all those tables. Thus would it be better to do this:

tbl_sessions: - this is the live table that is written to all the time, indexes on things like userid, accountid. this would only ever contain the current months records.

tbl_old_sessions: - this would replace the monthly table and be one giant table that contains all previous months records. Partioned by month (on a datefield populated by now()) and indexed on accountid, userid etc

Does this design make better sense than my previous one of monthly tables? should i be worried about partioning the tbl_old_sessions if i'm doing very little querying by actual date?

Answer 1

Your new design will make it easier to query across all of the tables without the need to know which tables to union when you write a query.

I'm not sure if you need to worry about the partitioning aspect. It sounds OK, but I've never worked with something like this on this scale (not in MySQL).

If you are worried about the partitioning, you could stay with your current solution, but instead of a new table for every year+month, have 12 tables, one for each month, for all years. At least your unions would be constant.