SET SESSION optimizer_switch='index_condition_pushdown=off';
SET SESSION join_cache_level=5;
SET SESSION join_buffer_size=1;
SET SESSION mrr_buffer_size=100000;
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) NOT NULL,
col_varchar_key varchar(1) NOT NULL,
col_varchar_nokey varchar(1) NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (10,8,'v','v'),(11,8,'f','f'),(12,5,'v','v'),(13,8,'s','s'),(14,8,'a','a'),(15,6,'p','p'),(16,7,'z','z'),(17,2,'a','a'),(18,5,'h','h'),(19,7,'h','h'),(20,2,'v','v'),(21,9,'v','v'),(22,142,'b','b'),(23,3,'y','y'),(24,0,'v','v'),(25,3,'m','m'),(26,5,'z','z'),(27,9,'n','n'),(28,1,'d','d'),(29,107,'a','a');
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) NOT NULL,
col_varchar_key varchar(1) NOT NULL,
col_varchar_nokey varchar(1) NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,9,'x','x'),(2,5,'g','g'),(3,1,'o','o'),(4,0,'g','g'),(5,1,'v','v'),(6,190,'m','m'),(7,6,'x','x'),(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'),(12,1,'g','g'),(13,8,'q','q'),(14,226,'m','m'),(15,133,'p','p'),(16,6,'e','e'),(17,3,'t','t'),(18,8,'j','j'),(19,5,'h','h'),(20,7,'w','w');
SET optimizer_switch='mrr_sort_keys=on';
EXPLAIN SELECT table2.pk , table1.col_int_key
FROM t2 AS table1 JOIN
( t1 AS table2 JOIN t2 AS table3 ON table3.col_varchar_nokey = table2.col_varchar_key )
ON table3.pk > table2.col_varchar_nokey
ORDER BY table1.pk ;
CREATE TABLE p1 AS SELECT table2.pk , table1.col_int_key
FROM t2 AS table1 JOIN
( t1 AS table2 JOIN t2 AS table3 ON table3.col_varchar_nokey = table2.col_varchar_key )
ON table3.pk > table2.col_varchar_nokey
ORDER BY table1.pk ;
SET optimizer_switch='mrr_sort_keys=off';
EXPLAIN SELECT table2.pk , table1.col_int_key
FROM t2 AS table1 JOIN
( t1 AS table2 JOIN t2 AS table3 ON table3.col_varchar_nokey = table2.col_varchar_key )
ON table3.pk > table2.col_varchar_nokey
ORDER BY table1.pk ;
CREATE TABLE p2 AS SELECT table2.pk , table1.col_int_key
FROM t2 AS table1 JOIN
( t1 AS table2 JOIN t2 AS table3 ON table3.col_varchar_nokey = table2.col_varchar_key )
ON table3.pk > table2.col_varchar_nokey
ORDER BY table1.pk ;
Test case:
--source include/ have_innodb. inc
SET SESSION optimizer_ switch= 'index_ condition_ pushdown= off'; size=100000;
SET SESSION join_cache_level=5;
SET SESSION join_buffer_size=1;
SET SESSION mrr_buffer_
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 ( key,col_ int_key) v','v') ,(11,8, 'f','f' ),(12,5, 'v','v' ),(13,8, 's','s' ),(14,8, 'a','a' ),(15,6, 'p','p' ),(16,7, 'z','z' ),(17,2, 'a','a' ),(18,5, 'h','h' ),(19,7, 'h','h' ),(20,2, 'v','v' ),(21,9, 'v','v' ),(22,142, 'b','b' ),(23,3, 'y','y' ),(24,0, 'v','v' ),(25,3, 'm','m' ),(26,5, 'z','z' ),(27,9, 'n','n' ),(28,1, 'd','d' ),(29,107, 'a','a' ); key,col_ int_key) ,'x'),( 2,5,'g' ,'g'),( 3,1,'o' ,'o'),( 4,0,'g' ,'g'),( 5,1,'v' ,'v'),( 6,190,' m','m') ,(7,6,' x','x') ,(8,3,' c','c') ,(9,4,' z','z') ,(10,3, 'i','i' ),(11,186, 'x','x' ),(12,1, 'g','g' ),(13,8, 'q','q' ),(14,226, 'm','m' ),(15,133, 'p','p' ),(16,6, 'e','e' ),(17,3, 't','t' ),(18,8, 'j','j' ),(19,5, 'h','h' ),(20,7, 'w','w' );
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) NOT NULL,
col_varchar_key varchar(1) NOT NULL,
col_varchar_nokey varchar(1) NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (10,8,'
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) NOT NULL,
col_varchar_key varchar(1) NOT NULL,
col_varchar_nokey varchar(1) NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,9,'x'
SET optimizer_ switch= 'mrr_sort_ keys=on' ;
EXPLAIN SELECT table2.pk , table1.col_int_key col_varchar_ nokey = table2. col_varchar_ key ) col_varchar_ nokey
FROM t2 AS table1 JOIN
( t1 AS table2 JOIN t2 AS table3 ON table3.
ON table3.pk > table2.
ORDER BY table1.pk ;
CREATE TABLE p1 AS SELECT table2.pk , table1.col_int_key col_varchar_ nokey = table2. col_varchar_ key ) col_varchar_ nokey
FROM t2 AS table1 JOIN
( t1 AS table2 JOIN t2 AS table3 ON table3.
ON table3.pk > table2.
ORDER BY table1.pk ;
SET optimizer_ switch= 'mrr_sort_ keys=off' ;
EXPLAIN SELECT table2.pk , table1.col_int_key col_varchar_ nokey = table2. col_varchar_ key ) col_varchar_ nokey
FROM t2 AS table1 JOIN
( t1 AS table2 JOIN t2 AS table3 ON table3.
ON table3.pk > table2.
ORDER BY table1.pk ;
CREATE TABLE p2 AS SELECT table2.pk , table1.col_int_key col_varchar_ nokey = table2. col_varchar_ key ) col_varchar_ nokey
FROM t2 AS table1 JOIN
( t1 AS table2 JOIN t2 AS table3 ON table3.
ON table3.pk > table2.
ORDER BY table1.pk ;
--let $diff_table_1 = test.p1 diff_tables. inc
--let $diff_table_2 = test.p2
--source include/