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?