April 17, 2014

Merge Tables Gotcha

I had the interesting customer case today which made me to do a bit research on the problem.

You can create merge table over MyISAM tables which contain primary key and global uniqueness would not be enforced in this case, this is as far as most people will think about it. In fact however it is worse than that – if you have same key values in underlying base tables some of the queries may give you wrong results:

In this case there are obviously two rows with value 1 in merge table however select querying by primary index returns only one.
Following EXPLAIN shows why this is the case – MySQL knows this is primary key and pre-reads the value in question as “const” value. Similar things happens in JOIN queries when it would use “eq_ref” lookup type which means it will only do one lookup for each key value as it knows it is unique.

In the case I was working on it was required to enforce uniqueness in the tables which are later queries as merge table so we could not drop PRIMARY KEY so I thought I should try using key on the given column instead:

This way it works fine.

Interesting enough first time I tried it I made a mistake of not defining column as NOT NULL for the merge table:
“create table tx (id int unsigned, key(id)) type=merge union(t1,t2)” If you create table this way it structure will be in fact different from underlying tables as PRIMARY KEY on the column automatically changes it to NOT NULL (which I think is bad behavior and it would better throw the error and ask to change the column type instead).

There is in fact the note about converting primary key to the key in MySQL manual

Here is what it says:

Note that the a column is indexed as a PRIMARY KEY in the underlying MyISAM tables, but not in the MERGE table. There it is indexed but not as a PRIMARY KEY because a MERGE table cannot enforce uniqueness over the set of underlying tables.

This however only speaks about enforcing uniqueness it does not tell anything about wrong results you may get.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Sergei Golubchik says:

    typos: “merge table over merge tables” and “underlying merge tables”.

  2. peter says:

    Thanks Sergei Fixed.

  3. Arie says:

    i have a strange problem with merge table. i create 5 tables from the same create statement and merge table on them.
    indexes cardinality equals none. when i alter the merge table and leave only any 4 tables (in the union) index cardinality becomes a number.

  4. Tim Little says:

    Arie, I realize that it’s been a year, but here’s my idea (something similar happened to me) :
    Check to see that all five tables have the same indexes as well as the same types for all columns.

    For us, what clinched it was when we made the merge on tables T1 + T2 + T3 + T4 + T5 failed then we removed one table at a time, then constructed the merge table with two tables at a time and checked the cardinalities.

Speak Your Mind

*