MySQL Merge Tables

The MySQL merge table is a powerful yet finicky and delicate beast. If you don’t feed it just right, it will jump up and bite your hand off, or it will totally ignore you.

So, what’s a merge table anyway? If you have a number of MySQL tables with identical definitions, you can create a merge table which encompasses the individual tables, and then you can perform certain SQL operations on the merge table instead of on each table.

For example, perhaps you are storing events, and you have a table for each month of the year. Perhaps these are named for months, eventsjanuary, eventsfebruary, and so on, all the way up to and including events*december. You can create a merge table on top of all of these, like this: ` create table events</em>year [definition] type=merge union=(eventsjanuary, eventsfebruary, …, eventsdecember). ` If you then run a select on events*year, you will get events from all of the months. You can do a number of other SQL operations on the merge table, including delete, insert, and update.

The problem is that MySQL is extremely picky about the definition of the component tables and of the merge table. So picky, in fact, that the tables can look identical when viewed with describe tables, yet MySQL will not see them as identical. If the tables don’t match the definition used to create the merge table, they will be left out of operations on the table, with no indication other than an incorrect result. This is not good.

Here’s what happened to me. I had two indices on each of the child tables. Let’s call then A and B. The merge table specified the indices as ` key(A), key(B) ` However, the code which created the indices created an index on B and then on A. The definitions were different, and the tables were ignored. PhpMyAdmin shows the table indices in their actual order, and this turned out to be an essential piece of information.

Once I got this straightened out, things were back to normal.

By the way, don’t let this list scare you away from merge tables. They work really well, and Syndic8 has tens of millions of headlines stored away in a series of such tables. I use one table per poll, and then I re-create the merge table at the end of each poll.