We have a MySQL database that is getting fairly large. I am
specifically worried about the size of 1 table. It is just over 3 gigs
and is the most heavily used table of the entire system. I think our
OS limits us to 4MB files, so we need to do some sort of archiving in
order to meet that requirement. However, we still need the data
accessible, and we don't want to sacrafice the performance of the
table (as it is large and already takes some time on some queries).

I am curious about the performance change we will experience if we
break the tables up and join them with a merge table.

1) Does anyone have any experience or empirical data based on this?
Will this speed up or slow down the table performance on selects? on
inserts or updates? I know that each subtable will have its own index.
I can't envision if that would make it quicker or slower. Since the
merge engine doesn't allow for inserts in the merge table, I assume
those would be quicker since we will only be opening 1 subtable on
each insert, yes?

2) What's the best size to split the tables up at? 3 tables of 1 gig
each? 30 tables of 100 megs each? We anticipate this table growing up
into 10+ gigs.

Thanks so much for your time. We are using Mysql 3.23 on a Linux system.

---

Clarification of Question by shane43-ga on 30 Sep 2004 10:36 PDT

Thanks for the interest in my problem. Here's more info:

This 1 table consists of about 40 columns. Most of the data is
integers which represent a flag or some status code. There are a
couple important data fields that are text's.

When we insert a new row, we usually update it a couple seconds later
based on an action we need to do to it. When we update it, it's only a
flag or 2 that we are updating. After that 1 update, it usually stays
pretty static. However, before we insert the entry, we run a select
statement to check the whole table to see if it already exists. That
eats up some time and resources I know.

We use an autoincrement field for the primary key. I am aware of the
limitations caused my a merge table on this value and plan to account
for it in code.

Table statistics:
Rows 2,327,686  
Row length 1,318  
Row size 1,472 Bytes 
Data 2,927 MB 
Index 349,360 KB 
Total 3,269 MB 

I would estimate we get about 5000 new entries per day.

We are willing to upgrade anything - if 4.x will help us somehow, we'll do it.

Thank you so much!

merge tables are good for logfiles and other data that usually never
changes after insert.
from my experience I can tell that SELECTs on tables become slow with
more than 3 million rows in the table, even on index-only reads.

to answer your question properly i could need some more information: 
what kind of data ist stored in this table?
do you update older entries?
do you use auto-increment fields?
how many rows does this table have? how many new per time period?

i'd like to help you with this.

btw, do you plan upgrading to mysql 4.x?

I had a table that got very large (7 million rows,  about 3G of data
at the time).   We have a database abstraction layer that everything
uses to access the table, so I added a hook that would automatically
select the table based on some attribute of the query being done, i.e.
I implemented a merge table myself (more or less), splitting the one
table into 10.  That worked really well at first; now it's grown to a
total of 12 million rows (6.2G of data) and it's getting sluggish
again, although we do relatively frequent updates of even older rows. 
Now I'm considering splitting it into 20 tables instead of 10, or
possibly 100.

The point of all this is that splitting tables up can help a lot, but
I can't vouch for the native merge table implementation because I've
never used it.