MySQL Forums :: Database Design & Data Modelling :: Multiple Tables Split By Date


Advanced Search

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


Subject Written By Posted
Multiple Tables Split By Date Billy Simons 02/20/2013 05:40AM
Re: Multiple Tables Split By Date Rick James 02/21/2013 11:23PM
Re: Multiple Tables Split By Date Billy Simons 02/22/2013 06:30AM
Re: Multiple Tables Split By Date Rick James 02/23/2013 12:10PM
Re: Multiple Tables Split By Date Billy Simons 02/25/2013 03:07AM
Re: Multiple Tables Split By Date Rick James 02/25/2013 11:48PM
Re: Multiple Tables Split By Date Billy Simons 02/26/2013 02:26AM


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.