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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | mysql> create table t1(id int unsigned primary key); Query OK, 0 rows affected (0.07 sec) mysql> create table t2(id int unsigned primary key); Query OK, 0 rows affected (0.02 sec) mysql> create table tm(id int unsigned not null primary key) type=merge union(t1,t2); Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.00 sec) mysql> insert into t2 values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from tm where id=1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> select * from tm; +----+ | id | +----+ | 1 | | 1 | +----+ 2 rows in set (0.00 sec) mysql> explain select * from tm where id=1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tm type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index 1 row in set (0.00 sec) |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | mysql> create table tx (id int unsigned not null, key(id)) type=merge union(t1,t2); Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> select * from tx where id=1; +----+ | id | +----+ | 1 | | 1 | +----+ 2 rows in set (0.00 sec) mysql> select * from tx; +------+ | id | +------+ | 1 | | 1 | +------+ 2 rows in set (0.00 sec) |
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.
typos: “merge table over merge tables” and “underlying merge tables”.
Thanks Sergei Fixed.
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.
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.