MySQL Forums
Forum List  »  Memory Storage Engine

One large table or several smaller ones?
Posted by: Andy Gaydos
Date: December 05, 2006 04:00AM

I am trying to decide if it is a good idea to split up one large table into several smaller ones to prevent performance issues. I am collecting groups of numeric (float) data once per minute and storing it sequentially into the database, using a datetime as the primary key. Once the data is inserted into the database, it is not modified. I need to be able to retrieve chunks of sequential data, i.e. all records between date1 and date2. I don't need to perform any JOINS.

The database spans several years, and includes many discrete tables (approx. 900) to handle the output of many different data collection instruments. So each table gets about 500,000 new records per year, with from 20 - 80 columns per table.

My question: will my above-mentioned SELECTS degrade significantly in performance as the database grows? (INSERT speed is not as much of an issue). Would it be significantly better re: performance to divide up each instrument's data table into years (i.e. into tables like instrumentA_2004, instrumentA_2005, etc.) versus having a single table (for each instrument) that contains all data from, say, years 2000-2015?

Right now I have all of 2006 data (approx 500,000 records per table) and the performance is perfectly acceptable. I just want to avoid any future problems.

Thanks in advance for any help.

Options: ReplyQuote

Written By
One large table or several smaller ones?
December 05, 2006 04:00AM

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.