I'm curious about the performance of MERGE tables. I currently have a table that's about 3 GB. I have indexed it to the extreme, and it is still slow on some queries. Will breaking it up into three tables of 1 GB each increase performance? What about say 10 tables of 300 MB each? What is the optimal size of MERGE tables, and how will it affect performance of selects and inserts?
Before trying something like using MERGE tables, I would make sure that your extreme indexing is actually working to it's fullest potential. Identify the queries that are running slow, and then use the EXPLAIN statement to determine if proper indexing is being used. You can find more information on using EXPLAIN at http://dev.mysql.com/doc/mysql/en/EXPLAIN.html. EXPLAIN is the best resource you have for determining why a query is running slowly.
On to MERGE tables: MERGE tables can produce significant improvements in limited situations, the main scenario involving logging. First a quick explanation of MERGE: A MERGE table is a special virtual table handler that takes a group of identical MyISAM tables and makes them appear as a single table. This means that you can query an individual table when you know it will have all the rows you are looking for, and query the merge table when the desired data is spread across multiple tables. So unless your data is split into definable chunks and you often only wish to query one of those chunks, you probably won't benefit from MERGE tables.
When you are dealing with such large data sets, you will benefit from not over-indexing, so remove any indexes that you are not actually using, and also look into more RAM. The more RAM you have, the larger the index cache you can use, which can dramatically speed up queries of large data sets. And of course faster I/O is also a big plus.
This was first published in October 2004