Bug #14084 index_merge access method not used for federated engine
Submitted: 17 Oct 2005 20:09 Modified: 23 Jan 2014 9:50
Reporter: Sergey Petrunya Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0-bk OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[17 Oct 2005 20:09] Sergey Petrunya
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)
[17 Oct 2005 20:15] Sergey Petrunya
The email at 
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=5&mail=43346
contains the fix that solves this particular example. 
The email also contains a list of additional concerns on index_merge over federated table handler.
[15 May 2006 17:31] Sergey Petrunya
After initial investigation it seems that there is a problem with cost calculations.

There will be no much merit in getting index_merge to run over federated table - the "right" way to run the query would be to push the table condition down to the backend server (and not retrieve two streams to front end and merge them here).