Multiple Tables Split By Date
Posted by: Billy Simons
Date: February 20, 2013 05:40AM

Hi Guys,

I have a question on table structures...

I have a massive table (60M+ rows) that i want to split up. This table contains transaction records that are queried quite often. The table is MyISAM replicated between a couple of different MySQL servers.

My though is to split the table in to multiple tables based on date, with their table name YYYYMM. This would be great and keep the tables manageable.

My question is how to query multiple tables to look for some of the data. Lets say, the time span for the records is 5 years, I would have 60 tables as they are broken down by month. One of the columns in all the tables has an account_id, and I want to know how many total records there are so I can show pagination.

I know I can union all the tables together etc, but my concern is that by unioning everything together to create one big table, whether this will hit the performance problems I am having at present as it will still be one big table.

Does anyone have any ideas on a good way to do this? I had though summary tables etc etc, but wanted to see if anyone else has done something similar.

Thanks in advance!

Options: ReplyQuote

Written By
Multiple Tables Split By Date
February 20, 2013 05:40AM
February 21, 2013 11:23PM
February 23, 2013 12:10PM
February 25, 2013 11:48PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.