Description:
index_merge access method is not used for federated engine, while it is used for query over underlying table.
How to repeat:
On backend server run:
CREATE TABLE t1 (
`id` int NOT NULL auto_increment,
`col1` int(10) NOT NULL DEFAULT 0,
`col2` varchar(64) NOT NULL DEFAULT '',
`col3` int(20) NOT NULL,
`col4` int(40) NOT NULL,
primary key (`id`, `col1`, `col2`, `col3`, `col4`),
key col1(col1),
key col2(col2),
key col3(col3),
key col4(col4));
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (1, 'one One', 11, 1111);
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (2, 'Two two', 22, 2222);
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (3, 'three Three', 33, 33333);
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (4, 'fourfourfour', 444, 4444444);
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (5, 'five 5 five five 5', 5, 55555);
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (6, 'six six Sixsix', 6666, 6);
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (7, 'seven Sevenseven', 77777, 7777);
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (8, 'eight eight eight', 88888, 88);
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (9, 'nine Nine', 999999, 999999);
INSERT INTO t1 (col1, col2, col3, col4)
VALUES (10, 'Tenth ten TEN', 1010101, 1010);
INSERT INTO t1(col1,col2,col3,col4)
SELECT col1+10,col2,col3+10,col4 FROM t1;
INSERT INTO t1(col1,col2,col3,col4)
SELECT col1+10,col2,col3+10,col4 FROM t1;
INSERT INTO t1(col1,col2,col3,col4)
SELECT col1+10,col2,col3+10,col4 FROM t1;
INSERT INTO t1(col1,col2,col3,col4)
SELECT col1+10,col2,col3+10,col4 FROM t1;
INSERT INTO t1(col1,col2,col3,col4)
SELECT col1+10,col2,col3+10,col4 FROM t1;
INSERT INTO t1(col1,col2,col3,col4)
SELECT col1+10,col2,col3+10,col4 FROM t1;
INSERT INTO t1(col1,col2,col3,col4)
SELECT col1+10,col2,col3+10,col4 FROM t1;
INSERT INTO t1(col1,col2,col3,col4)
SELECT col1+10,col2,col3+10,col4 FROM t1;
INSERT INTO t1(col1,col2,col3,col4)
SELECT col1+10,col2,col3+10,col4 FROM t1;
SELECT * FROM t1 WHERE col1=5 OR col3=77777;
On frontend server run:
CREATE TABLE t1 (
`id` int NOT NULL auto_increment,
`col1` int(10) NOT NULL DEFAULT 0,
`col2` varchar(64) NOT NULL DEFAULT '',
`col3` int(20) NOT NULL,
`col4` int(40) NOT NULL,
primary key (`id`, `col1`, `col2`, `col3`, `col4`),
key col1(col1),
key col2(col2),
key col3(col3),
key col4(col4))
ENGINE="FEDERATED"
CONNECTION='mysql://root@127.0.0.1:3307/test/t1';
See the query to use index_merge on backend server:
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
| 1 | SIMPLE | t1 | index_merge | col1,col3 | col1,col3 | 4,4 | NULL | 4 | Using union(col1,col3); Using where |
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
While on frontend server:
mysql> explain SELECT * FROM t1 WHERE col1=5 OR col3=77777;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | col1,col3 | NULL | NULL | NULL | 5120 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
