CREATE TABLE `B` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) NOT NULL,
`col_int_key` int(11) NOT NULL,
`col_date_key` date NOT NULL,
`col_date_nokey` date NOT NULL,
`col_time_key` time NOT NULL,
`col_time_nokey` time NOT NULL,
`col_datetime_key` datetime NOT NULL,
`col_datetime_nokey` datetime 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_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
);
INSERT INTO `B` VALUES (1,1,7,'1900-01-01','1900-01-01','01:13:38','01:13:38','2005-02-05 00:00:00','2005-02-05 00:00:00','p','p');
CREATE VIEW view_B AS SELECT * FROM B;
CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) NOT NULL,
`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=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,3,8,'v','v');
INSERT INTO `CC` VALUES (11,3,8,'f','f');
INSERT INTO `CC` VALUES (12,3,5,'v','v');
INSERT INTO `CC` VALUES (13,2,8,'s','s');
INSERT INTO `CC` VALUES (14,1,8,'a','a');
INSERT INTO `CC` VALUES (15,0,6,'p','p');
INSERT INTO `CC` VALUES (16,8,7,'z','z');
INSERT INTO `CC` VALUES (17,5,2,'a','a');
INSERT INTO `CC` VALUES (18,9,5,'h','h');
INSERT INTO `CC` VALUES (19,5,7,'h','h');
INSERT INTO `CC` VALUES (20,4,2,'v','v');
INSERT INTO `CC` VALUES (21,2,9,'v','v');
INSERT INTO `CC` VALUES (22,33,142,'b','b');
INSERT INTO `CC` VALUES (23,5,3,'y','y');
INSERT INTO `CC` VALUES (24,1,0,'v','v');
INSERT INTO `CC` VALUES (25,9,3,'m','m');
INSERT INTO `CC` VALUES (26,1,5,'z','z');
INSERT INTO `CC` VALUES (27,3,9,'n','n');
INSERT INTO `CC` VALUES (28,8,1,'d','d');
INSERT INTO `CC` VALUES (29,231,107,'a','a');
SET SESSION optimizer_switch = 'subquery_cache=off';
/* cache is ON */ SELECT COUNT(*)
FROM CC table1 STRAIGHT_JOIN ( view_B table2 STRAIGHT_JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` ) ON table3 .`col_varchar_nokey` = table2 .`col_varchar_key`
WHERE EXISTS (
SELECT DISTINCT `pk`
FROM CC
WHERE `pk` < table3 .`pk` ) OR table1 .`col_int_nokey` ;
/* cache is OFF */ SELECT COUNT(*)
FROM CC table1 STRAIGHT_JOIN ( view_B table2 STRAIGHT_JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` ) ON table3 .`col_varchar_nokey` = table2 .`col_varchar_key`
WHERE EXISTS (
SELECT DISTINCT `pk`
FROM CC
WHERE `pk` < table3 .`pk` ) OR table1 .`col_int_nokey` ;
Test case:
CREATE TABLE `B` ( datetime_ key` datetime NOT NULL, datetime_ nokey` datetime NOT NULL, varchar_ nokey` varchar(1) NOT NULL, key`), key`,`col_ int_key` )
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) NOT NULL,
`col_int_key` int(11) NOT NULL,
`col_date_key` date NOT NULL,
`col_date_nokey` date NOT NULL,
`col_time_key` time NOT NULL,
`col_time_nokey` time NOT NULL,
`col_
`col_
`col_varchar_key` varchar(1) NOT NULL,
`col_
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_
KEY `col_varchar_key` (`col_varchar_
);
INSERT INTO `B` VALUES (1,1,7, '1900-01- 01','1900- 01-01', '01:13: 38','01: 13:38', '2005-02- 05 00:00:00' ,'2005- 02-05 00:00:00','p','p');
CREATE VIEW view_B AS SELECT * FROM B;
CREATE TABLE `CC` ( varchar_ nokey` varchar(1) NOT NULL, key`,`col_ int_key` ) 142,'b' ,'b'); 107,'a' ,'a');
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) NOT NULL,
`col_int_key` int(11) NOT NULL,
`col_varchar_key` varchar(1) NOT NULL,
`col_
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,3,8,'v','v');
INSERT INTO `CC` VALUES (11,3,8,'f','f');
INSERT INTO `CC` VALUES (12,3,5,'v','v');
INSERT INTO `CC` VALUES (13,2,8,'s','s');
INSERT INTO `CC` VALUES (14,1,8,'a','a');
INSERT INTO `CC` VALUES (15,0,6,'p','p');
INSERT INTO `CC` VALUES (16,8,7,'z','z');
INSERT INTO `CC` VALUES (17,5,2,'a','a');
INSERT INTO `CC` VALUES (18,9,5,'h','h');
INSERT INTO `CC` VALUES (19,5,7,'h','h');
INSERT INTO `CC` VALUES (20,4,2,'v','v');
INSERT INTO `CC` VALUES (21,2,9,'v','v');
INSERT INTO `CC` VALUES (22,33,
INSERT INTO `CC` VALUES (23,5,3,'y','y');
INSERT INTO `CC` VALUES (24,1,0,'v','v');
INSERT INTO `CC` VALUES (25,9,3,'m','m');
INSERT INTO `CC` VALUES (26,1,5,'z','z');
INSERT INTO `CC` VALUES (27,3,9,'n','n');
INSERT INTO `CC` VALUES (28,8,1,'d','d');
INSERT INTO `CC` VALUES (29,231,
SET SESSION optimizer_switch = 'subquery_ cache=off' ;
/* cache is ON */ SELECT COUNT(*) nokey` = table2 .`col_varchar_key`
FROM CC table1 STRAIGHT_JOIN ( view_B table2 STRAIGHT_JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` ) ON table3 .`col_varchar_
WHERE EXISTS (
SELECT DISTINCT `pk`
FROM CC
WHERE `pk` < table3 .`pk` ) OR table1 .`col_int_nokey` ;
/* cache is OFF */ SELECT COUNT(*) nokey` = table2 .`col_varchar_key`
FROM CC table1 STRAIGHT_JOIN ( view_B table2 STRAIGHT_JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` ) ON table3 .`col_varchar_
WHERE EXISTS (
SELECT DISTINCT `pk`
FROM CC
WHERE `pk` < table3 .`pk` ) OR table1 .`col_int_nokey` ;