SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 136;
EXPLAIN SELECT t1.col_int_key AS field1 FROM t2 JOIN t1 ON t2.pk WHERE t2.col_int_key = 143 GROUP BY field1 LIMIT 10;
SELECT t1.col_int_key AS field1 FROM t2 JOIN t1 ON t2.pk WHERE t2.col_int_key = 143 GROUP BY field1 LIMIT 10;
Test case:
--source include/ have_innodb. inc
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 ( key,col_ int_key) ,(16,1, 'c'),(20, 2,'d'), (13,166, 'e'),(19, 6,'f'), (9,8,'h' ),(7,3, 'j'),(12, 5,'k'), (4,9,'k' ),(3,3, 'm'),(2, 9,'m'), (17,9,' m'),(14, 3,'n'), (10,53, 'o'),(5, NULL,'r' ),(15,0, 't'),(6, 9,'t'), (8,8,'u' ),(1,2, 'w'),(18, 5,'y'); key,col_ int_key)
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT 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 t1 VALUES (11,0,NULL)
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,7,'f');
SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 136;
EXPLAIN SELECT t1.col_int_key AS field1 FROM t2 JOIN t1 ON t2.pk WHERE t2.col_int_key = 143 GROUP BY field1 LIMIT 10;
SELECT t1.col_int_key AS field1 FROM t2 JOIN t1 ON t2.pk WHERE t2.col_int_key = 143 GROUP BY field1 LIMIT 10;
DROP TABLE t1;
DROP TABLE t2;