Description:
Results:
5.6.16, 5.7.6
mysql> set optimizer_switch='semijoin=on';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT STRAIGHT_JOIN COUNT(CC_1.c_v_c_key) AS field1 , CC_1.c_date_key AS field2 FROM (CC AS CC_1 INNER JOIN ((C INNER JOIN CC AS CC_2 ON (CC_2
.c_v_c_key = C.c_v_c_key))) ON (CC_2.c_v_c_key = C.c_v_c_nokey)) WHERE (CC_2.c_int_key NOT IN (SELECT SUB_B.pk AS SUBQUERY1_field1 FROM (CC AS SUB_CC_
3 STRAIGHT_JOIN B AS SUB_B ON (SUB_B.c_v_c_nokey = SUB_CC_3.c_v_c_key AND SUB_B.pk IN (SELECT CHILD_SUB_D.pk AS CHILD_SUBQUERY1_field1 FROM (CC AS CH
ILD_SUB_CC RIGHT JOIN D AS CHILD_SUB_D ON (CHILD_SUB_D.c_v_c_nokey = CHILD_SUB_CC.c_v_c_key)) WHERE CHILD_SUB_D.c_v_c_nokey <= 'p'))) WHERE SUB_CC_3.c
_v_c_nokey > CC_1.c_v_c_nokey OR SUB_CC_3.c_v_c_key >= SUB_B.c_v_c_nokey)) AND CC_1.c_v_c_key IN ('j') AND CC_1.pk IS NOT NULL GROUP BY field2;
+--------+------------+
| field1 | field2 |
+--------+------------+
| 8 | 2007-04-09 |
+--------+------------+
1 row in set (0.14 sec)
mysql> set optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT STRAIGHT_JOIN COUNT(CC_1.c_v_c_key) AS field1 , CC_1.c_date_key AS field2 FROM (CC AS CC_1 INNER JOIN ((C INNER JOIN CC AS CC_2 ON (CC_2
.c_v_c_key = C.c_v_c_key))) ON (CC_2.c_v_c_key = C.c_v_c_nokey)) WHERE (CC_2.c_int_key NOT IN (SELECT SUB_B.pk AS SUBQUERY1_field1 FROM (CC AS SUB_CC_
3 STRAIGHT_JOIN B AS SUB_B ON (SUB_B.c_v_c_nokey = SUB_CC_3.c_v_c_key AND SUB_B.pk IN (SELECT CHILD_SUB_D.pk AS CHILD_SUBQUERY1_field1 FROM (CC AS CH
ILD_SUB_CC RIGHT JOIN D AS CHILD_SUB_D ON (CHILD_SUB_D.c_v_c_nokey = CHILD_SUB_CC.c_v_c_key)) WHERE CHILD_SUB_D.c_v_c_nokey <= 'p'))) WHERE SUB_CC_3.c
_v_c_nokey > CC_1.c_v_c_nokey OR SUB_CC_3.c_v_c_key >= SUB_B.c_v_c_nokey)) AND CC_1.c_v_c_key IN ('j') AND CC_1.pk IS NOT NULL GROUP BY field2;
+--------+------------+
| field1 | field2 |
+--------+------------+
| 6 | 2007-04-09 |
+--------+------------+
1 row in set (0.00 sec)
How to repeat:
CREATE TABLE B (
pk int(11) NOT NULL AUTO_INCREMENT,
c_int_nokey int(11) DEFAULT NULL,
c_int_key int(11) DEFAULT NULL,
c_date_key date DEFAULT NULL,
c_date_nokey date DEFAULT NULL,
c_time_key time DEFAULT NULL,
c_time_nokey time DEFAULT NULL,
c_datetime_key datetime DEFAULT NULL,
c_datetime_nokey datetime DEFAULT NULL,
c_v_c_key varchar(1) DEFAULT NULL,
c_v_c_nokey varchar(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY c_int_key (c_int_key),
KEY c_date_key (c_date_key),
KEY c_time_key (c_time_key),
KEY c_datetime_key (c_datetime_key),
KEY c_v_c_key (c_v_c_key,c_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO B VALUES (1,1,7,'1900-01-01','1900-01-01',NULL,NULL,'2001-11-04 19:07:55','2001-11-04 19:07:55','x','x');
CREATE TABLE C (
pk int(11) NOT NULL AUTO_INCREMENT,
c_int_nokey int(11) DEFAULT NULL,
c_int_key int(11) DEFAULT NULL,
c_date_key date DEFAULT NULL,
c_date_nokey date DEFAULT NULL,
c_time_key time DEFAULT NULL,
c_time_nokey time DEFAULT NULL,
c_datetime_key datetime DEFAULT NULL,
c_datetime_nokey datetime DEFAULT NULL,
c_v_c_key varchar(1) DEFAULT NULL,
c_v_c_nokey varchar(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY c_int_key (c_int_key),
KEY c_date_key (c_date_key),
KEY c_time_key (c_time_key),
KEY c_datetime_key (c_datetime_key),
KEY c_v_c_key (c_v_c_key,c_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO C VALUES (1,2,4,'2008-12-05','2008-12-05','22:34:09','22:34:09','1900-01-01 00:00:00','1900-01-01 00:00:00','v','v'),(2,150,62,'2005-03-27','2005-03-27','14:26:02','14:26:02','2008-01-03 10:33:32','2008-01-03 10:33:32','v','v'),(3,NULL,7,'2004-04-09','2004-04-09','14:03:03','14:03:03','2001-11-28 00:50:27','2001-11-28 00:50:27','c','c'),(4,2,1,'2006-05-13','2006-05-13','01:46:09','01:46:09','2007-10-09 19:53:04','2007-10-09 19:53:04',NULL,NULL),(5,5,0,'2001-05-06','2001-05-06','16:21:18','16:21:18','2001-11-08 21:02:12','2001-11-08 21:02:12','x','x'),(6,3,7,'2006-03-03','2006-03-03','18:56:33','18:56:33','2003-04-01 00:00:00','2003-04-01 00:00:00','i','i'),(7,1,7,'2007-12-28','2007-12-28',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','e','e'),(8,4,1,'2004-10-20','2004-10-20','09:29:08','09:29:08','2007-07-12 00:00:00','2007-07-12 00:00:00','p','p'),(9,NULL,7,'2008-04-09','2008-04-09','19:11:10','19:11:10','2005-04-04 01:21:01','2005-04-04 01:21:01','s','s'),(10,2,1,'2005-12-25','2005-12-25','11:57:26','11:57:26','1900-01-01 00:00:00','1900-01-01 00:00:00','j','j'),(11,6,5,'1900-01-01','1900-01-01','00:39:46','00:39:46','2003-03-05 03:41:18','2003-03-05 03:41:18','z','z'),(12,6,2,NULL,NULL,'03:28:15','03:28:15','2008-08-03 11:33:04','2008-08-03 11:33:04','c','c'),(13,8,0,'1900-01-01','1900-01-01','06:44:18','06:44:18','2004-04-28 21:44:45','2004-04-28 21:44:45','a','a'),(14,2,1,'2001-01-16','2001-01-16','14:36:39','14:36:39','2001-04-06 00:00:00','2001-04-06 00:00:00','q','q'),(15,6,8,'1900-01-01','1900-01-01','18:42:45','18:42:45','2001-04-18 00:00:00','2001-04-18 00:00:00','y','y'),(16,8,1,'2001-11-23','2001-11-23','02:57:29','02:57:29','2008-12-18 19:39:55','2008-12-18 19:39:55',NULL,NULL),(17,3,1,'2004-11-04','2004-11-04','16:46:13','16:46:13','2000-08-01 12:19:39','2000-08-01 12:19:39','r','r'),(18,3,9,'2003-03-12','2003-03-12','19:39:02','19:39:02','2004-09-25 21:29:06','2004-09-25 21:29:06','v','v'),(19,9,1,'2002-06-22','2002-06-22',NULL,NULL,'2009-09-20 09:11:48','2009-09-20 09:11:48',NULL,NULL),(20,6,5,'2004-10-10','2004-10-10','20:58:33','20:58:33','2004-03-27 09:32:04','2004-03-27 09:32:04','r','r');
CREATE TABLE CC (
pk int(11) NOT NULL AUTO_INCREMENT,
c_int_nokey int(11) DEFAULT NULL,
c_int_key int(11) DEFAULT NULL,
c_date_key date DEFAULT NULL,
c_date_nokey date DEFAULT NULL,
c_time_key time DEFAULT NULL,
c_time_nokey time DEFAULT NULL,
c_datetime_key datetime DEFAULT NULL,
c_datetime_nokey datetime DEFAULT NULL,
c_v_c_key varchar(1) DEFAULT NULL,
c_v_c_nokey varchar(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY c_int_key (c_int_key),
KEY c_date_key (c_date_key),
KEY c_time_key (c_time_key),
KEY c_datetime_key (c_datetime_key),
KEY c_v_c_key (c_v_c_key,c_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO CC VALUES (10,NULL,8,'2000-12-03','2000-12-03','22:55:23','22:55:23','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),(11,8,7,'2008-05-03','2008-05-03','10:19:31','10:19:31','2007-10-06 17:56:40','2007-10-06 17:56:40','d','d'),(12,1,1,'2005-12-06','2005-12-06','14:40:36','14:40:36',NULL,NULL,'r','r'),(13,9,7,'2000-04-10','2000-04-10','04:37:47','04:37:47','2007-12-23 05:17:49','2007-12-23 05:17:49','f','f'),(14,4,9,'2002-11-05','2002-11-05','19:34:06','19:34:06',NULL,NULL,'y','y'),(15,3,NULL,'2000-09-06','2000-09-06','20:35:33','20:35:33','2000-08-16 00:00:00','2000-08-16 00:00:00','u','u'),(16,2,1,NULL,NULL,NULL,NULL,NULL,NULL,'m','m'),(17,NULL,9,'2007-06-14','2007-06-14','14:43:37','14:43:37','2003-02-07 09:50:39','2003-02-07 09:50:39',NULL,NULL),(18,2,2,'2007-11-17','2007-11-17','02:23:09','02:23:09','2003-08-04 23:00:25','2003-08-04 23:00:25','o','o'),(19,NULL,9,'2009-02-23','2009-02-23','01:22:45','01:22:45','2009-08-07 12:57:18','2009-08-07 12:57:18','w','w'),(20,6,2,'2007-01-08','2007-01-08','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','m','m'),(21,7,4,'2008-06-10','2008-06-10','00:13:25','00:13:25','2001-06-17 01:34:03','2001-06-17 01:34:03','q','q'),(22,2,0,'2002-10-20','2002-10-20','03:47:16','03:47:16',NULL,NULL,NULL,NULL),(23,5,4,'2008-09-12','2008-09-12','01:41:48','01:41:48','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d'),(24,7,8,'2006-06-16','2006-06-16','00:00:00','00:00:00',NULL,NULL,'g','g'),(25,6,NULL,'2004-09-18','2004-09-18','22:32:04','22:32:04','2007-08-22 14:10:22','2007-08-22 14:10:22','x','x'),(26,6,NULL,'1900-01-01','1900-01-01','16:44:14','16:44:14','2005-01-07 12:15:05','2005-01-07 12:15:05','f','f'),(27,2,0,'2005-09-13','2005-09-13','17:38:37','17:38:37','2008-07-18 00:00:00','2008-07-18 00:00:00','p','p'),(28,9,NULL,'2007-04-09','2007-04-09','08:46:48','08:46:48','2005-03-24 07:33:11','2005-03-24 07:33:11','j','j'),(29,6,8,'2000-09-20','2000-09-20','14:11:27','14:11:27','2003-06-13 23:19:49','2003-06-13 23:19:49','c','c');
CREATE TABLE D (
pk int(11) NOT NULL AUTO_INCREMENT,
c_int_nokey int(11) DEFAULT NULL,
c_int_key int(11) DEFAULT NULL,
c_date_key date DEFAULT NULL,
c_date_nokey date DEFAULT NULL,
c_time_key time DEFAULT NULL,
c_time_nokey time DEFAULT NULL,
c_datetime_key datetime DEFAULT NULL,
c_datetime_nokey datetime DEFAULT NULL,
c_v_c_key varchar(1) DEFAULT NULL,
c_v_c_nokey varchar(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY c_int_key (c_int_key),
KEY c_date_key (c_date_key),
KEY c_time_key (c_time_key),
KEY c_datetime_key (c_datetime_key),
KEY c_v_c_key (c_v_c_key,c_int_key)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO D VALUES (1,8,4,'2002-01-03','2002-01-03','01:01:02','01:01:02',NULL,NULL,'c','c'),(2,3,5,'2007-07-08','2007-07-08','03:22:30','03:22:30','2003-11-06 04:59:50','2003-11-06 04:59:50','c','c'),(3,3,8,'2000-08-02','2000-08-02','18:30:05','18:30:05','2007-08-13 00:00:00','2007-08-13 00:00:00','q','q'),(4,NULL,4,'2000-03-06','2000-03-06','07:30:51','07:30:51','2007-07-23 22:15:05','2007-07-23 22:15:05','g','g'),(5,7,8,'2002-06-05','2002-06-05',NULL,NULL,'2009-01-24 15:15:41','2009-01-24 15:15:41','e','e'),(6,4,2,'2009-08-09','2009-08-09','00:00:00','00:00:00','2004-04-25 15:55:43','2004-04-25 15:55:43','l','l'),(7,7,9,'2001-03-22','2001-03-22','02:43:46','02:43:46','2006-05-21 00:00:00','2006-05-21 00:00:00',NULL,NULL),(8,7,6,'2002-10-08','2002-10-08','23:08:56','23:08:56','2007-11-19 18:59:17','2007-11-19 18:59:17','v','v'),(9,8,NULL,NULL,NULL,'21:14:43','21:14:43','2004-10-11 00:00:00','2004-10-11 00:00:00','c','c'),(10,6,NULL,'2007-09-13','2007-09-13','10:37:15','10:37:15','1900-01-01 00:00:00','1900-01-01 00:00:00','u','u'),(11,3,48,'2003-12-08','2003-12-08','00:50:58','00:50:58','2003-11-22 00:42:24','2003-11-22 00:42:24','x','x'),(12,210,228,'2006-06-01','2006-06-01','15:11:05','15:11:05','2009-05-06 15:57:54','2009-05-06 15:57:54','x','x'),(13,1,3,'2001-10-04','2001-10-04','17:56:53','17:56:53','2000-12-26 00:33:56','2000-12-26 00:33:56','x','x'),(14,2,5,'2004-11-16','2004-11-16','01:40:28','01:40:28','2002-03-12 00:00:00','2002-03-12 00:00:00','l','l'),(15,251,39,NULL,NULL,'01:21:50','01:21:50','2005-06-12 11:13:17','2005-06-12 11:13:17','e','e'),(16,4,6,'2005-06-22','2005-06-22','21:17:36','21:17:36','2009-06-10 00:00:00','2009-06-10 00:00:00','s','s'),(17,4,8,'2003-04-19','2003-04-19','09:16:37','09:16:37','2002-04-07 00:00:00','2002-04-07 00:00:00','k','k'),(18,9,3,'2006-03-23','2006-03-23','16:41:37','16:41:37','2002-08-01 01:33:30','2002-08-01 01:33:30','m','m'),(19,4,NULL,NULL,NULL,'03:13:46','03:13:46','2003-04-05 11:02:01','2003-04-05 11:02:01','x','x'),(20,NULL,2,'2008-10-22','2008-10-22','17:27:08','17:27:08','2003-10-25 12:46:30','2003-10-25 12:46:30','s','s'),(21,4,6,'2009-04-04','2009-04-04','19:27:17','19:27:17','2007-08-10 00:00:00','2007-08-10 00:00:00','h','h'),(22,NULL,3,NULL,NULL,'00:51:44','00:51:44','2002-07-12 19:18:28','2002-07-12 19:18:28','u','u'),(23,1,1,'2001-07-15','2001-07-15','10:57:22','10:57:22','2004-06-24 00:00:00','2004-06-24 00:00:00','x','x'),(24,6,4,'2004-09-25','2004-09-25','13:28:27','13:28:27','2009-06-10 09:54:42','2009-06-10 09:54:42','l','l'),(25,2,3,'2001-02-20','2001-02-20','06:40:59','06:40:59','1900-01-01 00:00:00','1900-01-01 00:00:00','p','p'),(26,NULL,1,'1900-01-01','1900-01-01','22:56:45','22:56:45','2002-03-09 11:26:35','2002-03-09 11:26:35','i','i'),(27,4,NULL,'2004-08-02','2004-08-02','12:51:42','12:51:42','2004-02-22 21:45:20','2004-02-22 21:45:20','u','u'),(28,248,97,'2009-02-26','2009-02-26','01:52:32','01:52:32',NULL,NULL,'i','i'),(29,4,0,'2002-11-23','2002-11-23','20:42:07','20:42:07','2000-08-12 00:00:00','2000-08-12 00:00:00','i','i'),(30,8,0,'2008-12-17','2008-12-17','12:53:30','12:53:30','2003-05-24 18:11:13','2003-05-24 18:11:13','e','e'),(31,4,9,'2005-08-26','2005-08-26','02:10:47','02:10:47','2000-12-21 15:20:30','2000-12-21 15:20:30','h','h'),(32,5,5,'2001-03-10','2001-03-10','02:13:00','02:13:00','2006-10-27 05:05:38','2006-10-27 05:05:38','f','f'),(33,9,9,'1900-01-01','1900-01-01','08:21:57','08:21:57','2008-08-04 14:29:37','2008-08-04 14:29:37',NULL,NULL),(34,2,0,'2003-08-04','2003-08-04','01:48:05','01:48:05','2006-11-03 00:00:00','2006-11-03 00:00:00','p','p'),(35,4,2,'2007-11-06','2007-11-06','00:00:00','00:00:00','2009-11-26 19:28:11','2009-11-26 19:28:11','n','n'),(36,211,172,'2009-04-23','2009-04-23','00:00:00','00:00:00','2000-12-07 10:17:40','2000-12-07 10:17:40','h','h'),(37,2,NULL,'2002-10-06','2002-10-06','00:50:49','00:50:49',NULL,NULL,'m','m'),(38,4,5,'2008-02-12','2008-02-12',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','x','x'),(39,125,119,'2007-09-18','2007-09-18',NULL,NULL,'2007-11-27 00:00:00','2007-11-27 00:00:00','d','d'),(40,9,1,'2007-06-26','2007-06-26','07:26:24','07:26:24','2007-11-17 06:51:08','2007-11-17 06:51:08','d','d'),(41,4,4,NULL,NULL,'13:41:48','13:41:48','2009-12-03 06:39:37','2009-12-03 06:39:37','t','t'),(42,8,8,'2009-05-05','2009-05-05','04:03:35','04:03:35','2004-03-11 16:35:26','2004-03-11 16:35:26',NULL,NULL),(43,8,NULL,'2003-05-04','2003-05-04','03:45:26','03:45:26','2008-09-08 17:10:40','2008-09-08 17:10:40',NULL,NULL),(44,NULL,6,'2003-11-10','2003-11-10','00:00:00','00:00:00','2004-04-15 20:15:45','2004-04-15 20:15:45','v','v'),(45,8,5,'2009-02-19','2009-02-19','17:06:44','17:06:44','2005-04-19 21:52:38','2005-04-19 21:52:38','u','u'),(46,4,5,'2006-11-17','2006-11-17','18:47:54','18:47:54','2000-02-03 00:00:00','2000-02-03 00:00:00','p','p'),(47,8,1,'2000-02-23','2000-02-23','14:35:14','14:35:14','2002-06-10 17:21:09','2002-06-10 17:21:09','o','o'),(48,NULL,7,'1900-01-01','1900-01-01','07:50:59','07:50:59',NULL,NULL,'v','v'),(49,1,2,'2003-04-08','2003-04-08',NULL,NULL,'2005-02-09 17:44:31','2005-02-09 17:44:31','m','m'),(50,3,8,'2006-07-08','2006-07-08','12:35:26','12:35:26','2002-12-19 09:51:49','2002-12-19 09:51:49','x','x'),(51,5,9,'2001-05-21','2001-05-21','05:30:51','05:30:51',NULL,NULL,'n','n'),(52,8,NULL,NULL,NULL,'00:00:00','00:00:00','2005-06-27 07:34:53','2005-06-27 07:34:53','b','b'),(53,7,NULL,'2009-01-05','2009-01-05','12:42:04','12:42:04','1900-01-01 00:00:00','1900-01-01 00:00:00',NULL,NULL),(54,2,3,'2001-08-05','2001-08-05',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','r','r'),(55,NULL,172,'2000-11-01','2000-11-01','17:54:39','17:54:39','2003-07-08 10:33:13','2003-07-08 10:33:13','v','v'),(56,3,NULL,'2004-12-16','2004-12-16','17:47:16','17:47:16','2006-07-18 04:16:49','2006-07-18 04:16:49','a','a'),(57,NULL,6,'2003-02-06','2003-02-06','00:15:18','00:15:18',NULL,NULL,'u','u'),(58,9,6,'2008-04-23','2008-04-23','01:39:11','01:39:11','2004-05-19 17:08:35','2004-05-19 17:08:35',NULL,NULL),(59,1,5,'2005-12-20','2005-12-20','14:46:31','14:46:31',NULL,NULL,'b','b'),(60,0,4,'2002-03-13','2002-03-13','17:05:47','17:05:47','2006-04-27 12:42:58','2006-04-27 12:42:58','s','s'),(61,0,3,NULL,NULL,'05:32:59','05:32:59','2007-06-12 00:00:00','2007-06-12 00:00:00','t','t'),(62,8,2,'2006-07-20','2006-07-20','13:41:32','13:41:32','1900-01-01 00:00:00','1900-01-01 00:00:00','b','b'),(63,NULL,7,'2002-12-19','2002-12-19',NULL,NULL,'2001-05-18 02:54:34','2001-05-18 02:54:34','m','m'),(64,9,4,'2001-07-09','2001-07-09','04:25:19','04:25:19','2008-06-07 08:08:32','2008-06-07 08:08:32','v','v'),(65,1,6,'2006-05-27','2006-05-27','07:51:52','07:51:52','1900-01-01 00:00:00','1900-01-01 00:00:00','n','n'),(66,9,0,'2007-02-26','2007-02-26','16:19:53','16:19:53','2005-05-11 00:00:00','2005-05-11 00:00:00','j','j'),(67,7,8,NULL,NULL,'00:00:00','00:00:00','2003-12-17 21:47:57','2003-12-17 21:47:57',NULL,NULL),(68,2,5,'2007-02-07','2007-02-07','02:59:31','02:59:31',NULL,NULL,NULL,NULL),(69,1,8,'2005-01-22','2005-01-22','09:04:17','09:04:17','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h'),(70,9,2,'2006-04-25','2006-04-25','19:44:22','19:44:22',NULL,NULL,'k','k'),(71,5,9,'2002-11-13','2002-11-13','00:00:00','00:00:00','2009-06-08 21:21:31','2009-06-08 21:21:31','k','k'),(72,4,7,'2007-10-26','2007-10-26','00:00:00','00:00:00','2008-05-25 23:36:30','2008-05-25 23:36:30',NULL,NULL),(73,6,5,'2003-06-16','2003-06-16','22:16:47','22:16:47','2009-10-17 23:46:36','2009-10-17 23:46:36','n','n'),(74,5,7,'2006-01-18','2006-01-18','06:12:32','06:12:32','2009-02-28 00:00:00','2009-02-28 00:00:00','e','e'),(75,2,0,'1900-01-01','1900-01-01',NULL,NULL,'2004-04-14 13:27:34','2004-04-14 13:27:34','s','s'),(76,4,4,'2000-09-13','2000-09-13','02:38:48','02:38:48','2007-10-20 00:00:00','2007-10-20 00:00:00','w','w'),(77,0,3,'2003-01-26','2003-01-26','00:00:00','00:00:00','2007-11-11 20:10:29','2007-11-11 20:10:29','y','y'),(78,3,1,'2002-09-09','2002-09-09','02:02:49','02:02:49','2001-03-08 00:00:00','2001-03-08 00:00:00','z','z'),(79,0,0,'2001-09-06','2001-09-06','23:43:38','23:43:38','1900-01-01 00:00:00','1900-01-01 00:00:00','b','b'),(80,6,6,'2006-02-23','2006-02-23',NULL,NULL,'2006-07-21 19:26:57','2006-07-21 19:26:57','f','f'),(81,1,2,'2004-06-21','2004-06-21','12:35:01','12:35:01',NULL,NULL,'s','s'),(82,9,NULL,'2006-12-02','2006-12-02','00:18:04','00:18:04',NULL,NULL,'d','d'),(83,4,8,'2005-05-17','2005-05-17','11:53:29','11:53:29','2002-10-24 03:10:15','2002-10-24 03:10:15',NULL,NULL),(84,9,NULL,'2001-12-27','2001-12-27','13:53:19','13:53:19','2002-08-16 00:00:00','2002-08-16 00:00:00','d','d'),(85,4,NULL,'2008-04-11','2008-04-11',NULL,NULL,NULL,NULL,'n','n'),(86,1,NULL,'2000-09-24','2000-09-24','08:53:20','08:53:20','2005-09-09 12:56:20','2005-09-09 12:56:20','i','i'),(87,2,3,'2004-04-20','2004-04-20','10:38:45','10:38:45','2002-06-09 00:00:00','2002-06-09 00:00:00',NULL,NULL),(88,8,7,'2008-07-10','2008-07-10','22:27:36','22:27:36','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h'),(89,1,3,'2007-08-14','2007-08-14','18:06:56','18:06:56','2009-01-21 23:43:07','2009-01-21 23:43:07','d','d'),(90,0,5,'2008-06-08','2008-06-08','03:44:30','03:44:30',NULL,NULL,'c','c'),(91,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-09-06 20:49:33','2007-09-06 20:49:33','i','i'),(92,0,1,'2008-09-17','2008-09-17','22:16:07','22:16:07','2005-09-20 10:11:02','2005-09-20 10:11:02','t','t'),(93,1,2,NULL,NULL,'17:14:13','17:14:13','2007-10-11 00:00:00','2007-10-11 00:00:00','g','g'),(94,2,1,'2004-02-25','2004-02-25','14:49:31','14:49:31','2005-06-13 00:26:39','2005-06-13 00:26:39','q','q'),(95,NULL,7,'2009-11-02','2009-11-02','00:27:36','00:27:36','2001-06-18 08:40:49','2001-06-18 08:40:49','l','l'),(96,3,1,'2001-12-14','2001-12-14','13:59:24','13:59:24','2006-06-07 00:00:00','2006-06-07 00:00:00','n','n'),(97,8,9,NULL,NULL,'01:48:46','01:48:46','2004-03-18 22:35:04','2004-03-18 22:35:04','z','z'),(98,4,9,'1900-01-01','1900-01-01',NULL,NULL,NULL,NULL,'n','n'),(99,4,8,'2002-04-12','2002-04-12','11:07:46','11:07:46','2001-07-15 12:46:49','2001-07-15 12:46:49','r','r'),(100,NULL,3,'2000-02-09','2000-02-09',NULL,NULL,'2001-03-27 06:53:22','2001-03-27 06:53:22','p','p');
ANALYZE TABLE B,C,CC,D;
SELECT
STRAIGHT_JOIN
COUNT(CC_1.c_v_c_key) AS field1 ,
CC_1.c_date_key AS field2
FROM
(CC AS CC_1 INNER JOIN
((C INNER JOIN CC AS CC_2 ON (CC_2.c_v_c_key = C.c_v_c_key)))
ON (CC_2.c_v_c_key = C.c_v_c_nokey))
WHERE
(CC_2.c_int_key NOT IN
(
SELECT SUB_B.pk AS SUBQUERY1_field1
FROM
(CC AS SUB_CC_3 STRAIGHT_JOIN B AS SUB_B
ON (SUB_B.c_v_c_nokey = SUB_CC_3.c_v_c_key AND
SUB_B.pk IN
(SELECT CHILD_SUB_D.pk AS CHILD_SUBQUERY1_field1
FROM
(CC AS CHILD_SUB_CC RIGHT JOIN D AS CHILD_SUB_D
ON (CHILD_SUB_D.c_v_c_nokey = CHILD_SUB_CC.c_v_c_key))
WHERE CHILD_SUB_D.c_v_c_nokey <= 'p')))
WHERE SUB_CC_3.c_v_c_nokey > CC_1.c_v_c_nokey OR
SUB_CC_3.c_v_c_key >= SUB_B.c_v_c_nokey))
AND CC_1.c_v_c_key IN ('j')
AND CC_1.pk IS NOT NULL
GROUP BY field2;
Suggested fix:
Fix.
Description: Results: 5.6.16, 5.7.6 mysql> set optimizer_switch='semijoin=on'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT STRAIGHT_JOIN COUNT(CC_1.c_v_c_key) AS field1 , CC_1.c_date_key AS field2 FROM (CC AS CC_1 INNER JOIN ((C INNER JOIN CC AS CC_2 ON (CC_2 .c_v_c_key = C.c_v_c_key))) ON (CC_2.c_v_c_key = C.c_v_c_nokey)) WHERE (CC_2.c_int_key NOT IN (SELECT SUB_B.pk AS SUBQUERY1_field1 FROM (CC AS SUB_CC_ 3 STRAIGHT_JOIN B AS SUB_B ON (SUB_B.c_v_c_nokey = SUB_CC_3.c_v_c_key AND SUB_B.pk IN (SELECT CHILD_SUB_D.pk AS CHILD_SUBQUERY1_field1 FROM (CC AS CH ILD_SUB_CC RIGHT JOIN D AS CHILD_SUB_D ON (CHILD_SUB_D.c_v_c_nokey = CHILD_SUB_CC.c_v_c_key)) WHERE CHILD_SUB_D.c_v_c_nokey <= 'p'))) WHERE SUB_CC_3.c _v_c_nokey > CC_1.c_v_c_nokey OR SUB_CC_3.c_v_c_key >= SUB_B.c_v_c_nokey)) AND CC_1.c_v_c_key IN ('j') AND CC_1.pk IS NOT NULL GROUP BY field2; +--------+------------+ | field1 | field2 | +--------+------------+ | 8 | 2007-04-09 | +--------+------------+ 1 row in set (0.14 sec) mysql> set optimizer_switch='semijoin=off'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT STRAIGHT_JOIN COUNT(CC_1.c_v_c_key) AS field1 , CC_1.c_date_key AS field2 FROM (CC AS CC_1 INNER JOIN ((C INNER JOIN CC AS CC_2 ON (CC_2 .c_v_c_key = C.c_v_c_key))) ON (CC_2.c_v_c_key = C.c_v_c_nokey)) WHERE (CC_2.c_int_key NOT IN (SELECT SUB_B.pk AS SUBQUERY1_field1 FROM (CC AS SUB_CC_ 3 STRAIGHT_JOIN B AS SUB_B ON (SUB_B.c_v_c_nokey = SUB_CC_3.c_v_c_key AND SUB_B.pk IN (SELECT CHILD_SUB_D.pk AS CHILD_SUBQUERY1_field1 FROM (CC AS CH ILD_SUB_CC RIGHT JOIN D AS CHILD_SUB_D ON (CHILD_SUB_D.c_v_c_nokey = CHILD_SUB_CC.c_v_c_key)) WHERE CHILD_SUB_D.c_v_c_nokey <= 'p'))) WHERE SUB_CC_3.c _v_c_nokey > CC_1.c_v_c_nokey OR SUB_CC_3.c_v_c_key >= SUB_B.c_v_c_nokey)) AND CC_1.c_v_c_key IN ('j') AND CC_1.pk IS NOT NULL GROUP BY field2; +--------+------------+ | field1 | field2 | +--------+------------+ | 6 | 2007-04-09 | +--------+------------+ 1 row in set (0.00 sec) How to repeat: CREATE TABLE B ( pk int(11) NOT NULL AUTO_INCREMENT, c_int_nokey int(11) DEFAULT NULL, c_int_key int(11) DEFAULT NULL, c_date_key date DEFAULT NULL, c_date_nokey date DEFAULT NULL, c_time_key time DEFAULT NULL, c_time_nokey time DEFAULT NULL, c_datetime_key datetime DEFAULT NULL, c_datetime_nokey datetime DEFAULT NULL, c_v_c_key varchar(1) DEFAULT NULL, c_v_c_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY c_int_key (c_int_key), KEY c_date_key (c_date_key), KEY c_time_key (c_time_key), KEY c_datetime_key (c_datetime_key), KEY c_v_c_key (c_v_c_key,c_int_key) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; INSERT INTO B VALUES (1,1,7,'1900-01-01','1900-01-01',NULL,NULL,'2001-11-04 19:07:55','2001-11-04 19:07:55','x','x'); CREATE TABLE C ( pk int(11) NOT NULL AUTO_INCREMENT, c_int_nokey int(11) DEFAULT NULL, c_int_key int(11) DEFAULT NULL, c_date_key date DEFAULT NULL, c_date_nokey date DEFAULT NULL, c_time_key time DEFAULT NULL, c_time_nokey time DEFAULT NULL, c_datetime_key datetime DEFAULT NULL, c_datetime_nokey datetime DEFAULT NULL, c_v_c_key varchar(1) DEFAULT NULL, c_v_c_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY c_int_key (c_int_key), KEY c_date_key (c_date_key), KEY c_time_key (c_time_key), KEY c_datetime_key (c_datetime_key), KEY c_v_c_key (c_v_c_key,c_int_key) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; INSERT INTO C VALUES (1,2,4,'2008-12-05','2008-12-05','22:34:09','22:34:09','1900-01-01 00:00:00','1900-01-01 00:00:00','v','v'),(2,150,62,'2005-03-27','2005-03-27','14:26:02','14:26:02','2008-01-03 10:33:32','2008-01-03 10:33:32','v','v'),(3,NULL,7,'2004-04-09','2004-04-09','14:03:03','14:03:03','2001-11-28 00:50:27','2001-11-28 00:50:27','c','c'),(4,2,1,'2006-05-13','2006-05-13','01:46:09','01:46:09','2007-10-09 19:53:04','2007-10-09 19:53:04',NULL,NULL),(5,5,0,'2001-05-06','2001-05-06','16:21:18','16:21:18','2001-11-08 21:02:12','2001-11-08 21:02:12','x','x'),(6,3,7,'2006-03-03','2006-03-03','18:56:33','18:56:33','2003-04-01 00:00:00','2003-04-01 00:00:00','i','i'),(7,1,7,'2007-12-28','2007-12-28',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','e','e'),(8,4,1,'2004-10-20','2004-10-20','09:29:08','09:29:08','2007-07-12 00:00:00','2007-07-12 00:00:00','p','p'),(9,NULL,7,'2008-04-09','2008-04-09','19:11:10','19:11:10','2005-04-04 01:21:01','2005-04-04 01:21:01','s','s'),(10,2,1,'2005-12-25','2005-12-25','11:57:26','11:57:26','1900-01-01 00:00:00','1900-01-01 00:00:00','j','j'),(11,6,5,'1900-01-01','1900-01-01','00:39:46','00:39:46','2003-03-05 03:41:18','2003-03-05 03:41:18','z','z'),(12,6,2,NULL,NULL,'03:28:15','03:28:15','2008-08-03 11:33:04','2008-08-03 11:33:04','c','c'),(13,8,0,'1900-01-01','1900-01-01','06:44:18','06:44:18','2004-04-28 21:44:45','2004-04-28 21:44:45','a','a'),(14,2,1,'2001-01-16','2001-01-16','14:36:39','14:36:39','2001-04-06 00:00:00','2001-04-06 00:00:00','q','q'),(15,6,8,'1900-01-01','1900-01-01','18:42:45','18:42:45','2001-04-18 00:00:00','2001-04-18 00:00:00','y','y'),(16,8,1,'2001-11-23','2001-11-23','02:57:29','02:57:29','2008-12-18 19:39:55','2008-12-18 19:39:55',NULL,NULL),(17,3,1,'2004-11-04','2004-11-04','16:46:13','16:46:13','2000-08-01 12:19:39','2000-08-01 12:19:39','r','r'),(18,3,9,'2003-03-12','2003-03-12','19:39:02','19:39:02','2004-09-25 21:29:06','2004-09-25 21:29:06','v','v'),(19,9,1,'2002-06-22','2002-06-22',NULL,NULL,'2009-09-20 09:11:48','2009-09-20 09:11:48',NULL,NULL),(20,6,5,'2004-10-10','2004-10-10','20:58:33','20:58:33','2004-03-27 09:32:04','2004-03-27 09:32:04','r','r'); CREATE TABLE CC ( pk int(11) NOT NULL AUTO_INCREMENT, c_int_nokey int(11) DEFAULT NULL, c_int_key int(11) DEFAULT NULL, c_date_key date DEFAULT NULL, c_date_nokey date DEFAULT NULL, c_time_key time DEFAULT NULL, c_time_nokey time DEFAULT NULL, c_datetime_key datetime DEFAULT NULL, c_datetime_nokey datetime DEFAULT NULL, c_v_c_key varchar(1) DEFAULT NULL, c_v_c_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY c_int_key (c_int_key), KEY c_date_key (c_date_key), KEY c_time_key (c_time_key), KEY c_datetime_key (c_datetime_key), KEY c_v_c_key (c_v_c_key,c_int_key) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; INSERT INTO CC VALUES (10,NULL,8,'2000-12-03','2000-12-03','22:55:23','22:55:23','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),(11,8,7,'2008-05-03','2008-05-03','10:19:31','10:19:31','2007-10-06 17:56:40','2007-10-06 17:56:40','d','d'),(12,1,1,'2005-12-06','2005-12-06','14:40:36','14:40:36',NULL,NULL,'r','r'),(13,9,7,'2000-04-10','2000-04-10','04:37:47','04:37:47','2007-12-23 05:17:49','2007-12-23 05:17:49','f','f'),(14,4,9,'2002-11-05','2002-11-05','19:34:06','19:34:06',NULL,NULL,'y','y'),(15,3,NULL,'2000-09-06','2000-09-06','20:35:33','20:35:33','2000-08-16 00:00:00','2000-08-16 00:00:00','u','u'),(16,2,1,NULL,NULL,NULL,NULL,NULL,NULL,'m','m'),(17,NULL,9,'2007-06-14','2007-06-14','14:43:37','14:43:37','2003-02-07 09:50:39','2003-02-07 09:50:39',NULL,NULL),(18,2,2,'2007-11-17','2007-11-17','02:23:09','02:23:09','2003-08-04 23:00:25','2003-08-04 23:00:25','o','o'),(19,NULL,9,'2009-02-23','2009-02-23','01:22:45','01:22:45','2009-08-07 12:57:18','2009-08-07 12:57:18','w','w'),(20,6,2,'2007-01-08','2007-01-08','00:00:00','00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00','m','m'),(21,7,4,'2008-06-10','2008-06-10','00:13:25','00:13:25','2001-06-17 01:34:03','2001-06-17 01:34:03','q','q'),(22,2,0,'2002-10-20','2002-10-20','03:47:16','03:47:16',NULL,NULL,NULL,NULL),(23,5,4,'2008-09-12','2008-09-12','01:41:48','01:41:48','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d'),(24,7,8,'2006-06-16','2006-06-16','00:00:00','00:00:00',NULL,NULL,'g','g'),(25,6,NULL,'2004-09-18','2004-09-18','22:32:04','22:32:04','2007-08-22 14:10:22','2007-08-22 14:10:22','x','x'),(26,6,NULL,'1900-01-01','1900-01-01','16:44:14','16:44:14','2005-01-07 12:15:05','2005-01-07 12:15:05','f','f'),(27,2,0,'2005-09-13','2005-09-13','17:38:37','17:38:37','2008-07-18 00:00:00','2008-07-18 00:00:00','p','p'),(28,9,NULL,'2007-04-09','2007-04-09','08:46:48','08:46:48','2005-03-24 07:33:11','2005-03-24 07:33:11','j','j'),(29,6,8,'2000-09-20','2000-09-20','14:11:27','14:11:27','2003-06-13 23:19:49','2003-06-13 23:19:49','c','c'); CREATE TABLE D ( pk int(11) NOT NULL AUTO_INCREMENT, c_int_nokey int(11) DEFAULT NULL, c_int_key int(11) DEFAULT NULL, c_date_key date DEFAULT NULL, c_date_nokey date DEFAULT NULL, c_time_key time DEFAULT NULL, c_time_nokey time DEFAULT NULL, c_datetime_key datetime DEFAULT NULL, c_datetime_nokey datetime DEFAULT NULL, c_v_c_key varchar(1) DEFAULT NULL, c_v_c_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY c_int_key (c_int_key), KEY c_date_key (c_date_key), KEY c_time_key (c_time_key), KEY c_datetime_key (c_datetime_key), KEY c_v_c_key (c_v_c_key,c_int_key) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; INSERT INTO D VALUES (1,8,4,'2002-01-03','2002-01-03','01:01:02','01:01:02',NULL,NULL,'c','c'),(2,3,5,'2007-07-08','2007-07-08','03:22:30','03:22:30','2003-11-06 04:59:50','2003-11-06 04:59:50','c','c'),(3,3,8,'2000-08-02','2000-08-02','18:30:05','18:30:05','2007-08-13 00:00:00','2007-08-13 00:00:00','q','q'),(4,NULL,4,'2000-03-06','2000-03-06','07:30:51','07:30:51','2007-07-23 22:15:05','2007-07-23 22:15:05','g','g'),(5,7,8,'2002-06-05','2002-06-05',NULL,NULL,'2009-01-24 15:15:41','2009-01-24 15:15:41','e','e'),(6,4,2,'2009-08-09','2009-08-09','00:00:00','00:00:00','2004-04-25 15:55:43','2004-04-25 15:55:43','l','l'),(7,7,9,'2001-03-22','2001-03-22','02:43:46','02:43:46','2006-05-21 00:00:00','2006-05-21 00:00:00',NULL,NULL),(8,7,6,'2002-10-08','2002-10-08','23:08:56','23:08:56','2007-11-19 18:59:17','2007-11-19 18:59:17','v','v'),(9,8,NULL,NULL,NULL,'21:14:43','21:14:43','2004-10-11 00:00:00','2004-10-11 00:00:00','c','c'),(10,6,NULL,'2007-09-13','2007-09-13','10:37:15','10:37:15','1900-01-01 00:00:00','1900-01-01 00:00:00','u','u'),(11,3,48,'2003-12-08','2003-12-08','00:50:58','00:50:58','2003-11-22 00:42:24','2003-11-22 00:42:24','x','x'),(12,210,228,'2006-06-01','2006-06-01','15:11:05','15:11:05','2009-05-06 15:57:54','2009-05-06 15:57:54','x','x'),(13,1,3,'2001-10-04','2001-10-04','17:56:53','17:56:53','2000-12-26 00:33:56','2000-12-26 00:33:56','x','x'),(14,2,5,'2004-11-16','2004-11-16','01:40:28','01:40:28','2002-03-12 00:00:00','2002-03-12 00:00:00','l','l'),(15,251,39,NULL,NULL,'01:21:50','01:21:50','2005-06-12 11:13:17','2005-06-12 11:13:17','e','e'),(16,4,6,'2005-06-22','2005-06-22','21:17:36','21:17:36','2009-06-10 00:00:00','2009-06-10 00:00:00','s','s'),(17,4,8,'2003-04-19','2003-04-19','09:16:37','09:16:37','2002-04-07 00:00:00','2002-04-07 00:00:00','k','k'),(18,9,3,'2006-03-23','2006-03-23','16:41:37','16:41:37','2002-08-01 01:33:30','2002-08-01 01:33:30','m','m'),(19,4,NULL,NULL,NULL,'03:13:46','03:13:46','2003-04-05 11:02:01','2003-04-05 11:02:01','x','x'),(20,NULL,2,'2008-10-22','2008-10-22','17:27:08','17:27:08','2003-10-25 12:46:30','2003-10-25 12:46:30','s','s'),(21,4,6,'2009-04-04','2009-04-04','19:27:17','19:27:17','2007-08-10 00:00:00','2007-08-10 00:00:00','h','h'),(22,NULL,3,NULL,NULL,'00:51:44','00:51:44','2002-07-12 19:18:28','2002-07-12 19:18:28','u','u'),(23,1,1,'2001-07-15','2001-07-15','10:57:22','10:57:22','2004-06-24 00:00:00','2004-06-24 00:00:00','x','x'),(24,6,4,'2004-09-25','2004-09-25','13:28:27','13:28:27','2009-06-10 09:54:42','2009-06-10 09:54:42','l','l'),(25,2,3,'2001-02-20','2001-02-20','06:40:59','06:40:59','1900-01-01 00:00:00','1900-01-01 00:00:00','p','p'),(26,NULL,1,'1900-01-01','1900-01-01','22:56:45','22:56:45','2002-03-09 11:26:35','2002-03-09 11:26:35','i','i'),(27,4,NULL,'2004-08-02','2004-08-02','12:51:42','12:51:42','2004-02-22 21:45:20','2004-02-22 21:45:20','u','u'),(28,248,97,'2009-02-26','2009-02-26','01:52:32','01:52:32',NULL,NULL,'i','i'),(29,4,0,'2002-11-23','2002-11-23','20:42:07','20:42:07','2000-08-12 00:00:00','2000-08-12 00:00:00','i','i'),(30,8,0,'2008-12-17','2008-12-17','12:53:30','12:53:30','2003-05-24 18:11:13','2003-05-24 18:11:13','e','e'),(31,4,9,'2005-08-26','2005-08-26','02:10:47','02:10:47','2000-12-21 15:20:30','2000-12-21 15:20:30','h','h'),(32,5,5,'2001-03-10','2001-03-10','02:13:00','02:13:00','2006-10-27 05:05:38','2006-10-27 05:05:38','f','f'),(33,9,9,'1900-01-01','1900-01-01','08:21:57','08:21:57','2008-08-04 14:29:37','2008-08-04 14:29:37',NULL,NULL),(34,2,0,'2003-08-04','2003-08-04','01:48:05','01:48:05','2006-11-03 00:00:00','2006-11-03 00:00:00','p','p'),(35,4,2,'2007-11-06','2007-11-06','00:00:00','00:00:00','2009-11-26 19:28:11','2009-11-26 19:28:11','n','n'),(36,211,172,'2009-04-23','2009-04-23','00:00:00','00:00:00','2000-12-07 10:17:40','2000-12-07 10:17:40','h','h'),(37,2,NULL,'2002-10-06','2002-10-06','00:50:49','00:50:49',NULL,NULL,'m','m'),(38,4,5,'2008-02-12','2008-02-12',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','x','x'),(39,125,119,'2007-09-18','2007-09-18',NULL,NULL,'2007-11-27 00:00:00','2007-11-27 00:00:00','d','d'),(40,9,1,'2007-06-26','2007-06-26','07:26:24','07:26:24','2007-11-17 06:51:08','2007-11-17 06:51:08','d','d'),(41,4,4,NULL,NULL,'13:41:48','13:41:48','2009-12-03 06:39:37','2009-12-03 06:39:37','t','t'),(42,8,8,'2009-05-05','2009-05-05','04:03:35','04:03:35','2004-03-11 16:35:26','2004-03-11 16:35:26',NULL,NULL),(43,8,NULL,'2003-05-04','2003-05-04','03:45:26','03:45:26','2008-09-08 17:10:40','2008-09-08 17:10:40',NULL,NULL),(44,NULL,6,'2003-11-10','2003-11-10','00:00:00','00:00:00','2004-04-15 20:15:45','2004-04-15 20:15:45','v','v'),(45,8,5,'2009-02-19','2009-02-19','17:06:44','17:06:44','2005-04-19 21:52:38','2005-04-19 21:52:38','u','u'),(46,4,5,'2006-11-17','2006-11-17','18:47:54','18:47:54','2000-02-03 00:00:00','2000-02-03 00:00:00','p','p'),(47,8,1,'2000-02-23','2000-02-23','14:35:14','14:35:14','2002-06-10 17:21:09','2002-06-10 17:21:09','o','o'),(48,NULL,7,'1900-01-01','1900-01-01','07:50:59','07:50:59',NULL,NULL,'v','v'),(49,1,2,'2003-04-08','2003-04-08',NULL,NULL,'2005-02-09 17:44:31','2005-02-09 17:44:31','m','m'),(50,3,8,'2006-07-08','2006-07-08','12:35:26','12:35:26','2002-12-19 09:51:49','2002-12-19 09:51:49','x','x'),(51,5,9,'2001-05-21','2001-05-21','05:30:51','05:30:51',NULL,NULL,'n','n'),(52,8,NULL,NULL,NULL,'00:00:00','00:00:00','2005-06-27 07:34:53','2005-06-27 07:34:53','b','b'),(53,7,NULL,'2009-01-05','2009-01-05','12:42:04','12:42:04','1900-01-01 00:00:00','1900-01-01 00:00:00',NULL,NULL),(54,2,3,'2001-08-05','2001-08-05',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','r','r'),(55,NULL,172,'2000-11-01','2000-11-01','17:54:39','17:54:39','2003-07-08 10:33:13','2003-07-08 10:33:13','v','v'),(56,3,NULL,'2004-12-16','2004-12-16','17:47:16','17:47:16','2006-07-18 04:16:49','2006-07-18 04:16:49','a','a'),(57,NULL,6,'2003-02-06','2003-02-06','00:15:18','00:15:18',NULL,NULL,'u','u'),(58,9,6,'2008-04-23','2008-04-23','01:39:11','01:39:11','2004-05-19 17:08:35','2004-05-19 17:08:35',NULL,NULL),(59,1,5,'2005-12-20','2005-12-20','14:46:31','14:46:31',NULL,NULL,'b','b'),(60,0,4,'2002-03-13','2002-03-13','17:05:47','17:05:47','2006-04-27 12:42:58','2006-04-27 12:42:58','s','s'),(61,0,3,NULL,NULL,'05:32:59','05:32:59','2007-06-12 00:00:00','2007-06-12 00:00:00','t','t'),(62,8,2,'2006-07-20','2006-07-20','13:41:32','13:41:32','1900-01-01 00:00:00','1900-01-01 00:00:00','b','b'),(63,NULL,7,'2002-12-19','2002-12-19',NULL,NULL,'2001-05-18 02:54:34','2001-05-18 02:54:34','m','m'),(64,9,4,'2001-07-09','2001-07-09','04:25:19','04:25:19','2008-06-07 08:08:32','2008-06-07 08:08:32','v','v'),(65,1,6,'2006-05-27','2006-05-27','07:51:52','07:51:52','1900-01-01 00:00:00','1900-01-01 00:00:00','n','n'),(66,9,0,'2007-02-26','2007-02-26','16:19:53','16:19:53','2005-05-11 00:00:00','2005-05-11 00:00:00','j','j'),(67,7,8,NULL,NULL,'00:00:00','00:00:00','2003-12-17 21:47:57','2003-12-17 21:47:57',NULL,NULL),(68,2,5,'2007-02-07','2007-02-07','02:59:31','02:59:31',NULL,NULL,NULL,NULL),(69,1,8,'2005-01-22','2005-01-22','09:04:17','09:04:17','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h'),(70,9,2,'2006-04-25','2006-04-25','19:44:22','19:44:22',NULL,NULL,'k','k'),(71,5,9,'2002-11-13','2002-11-13','00:00:00','00:00:00','2009-06-08 21:21:31','2009-06-08 21:21:31','k','k'),(72,4,7,'2007-10-26','2007-10-26','00:00:00','00:00:00','2008-05-25 23:36:30','2008-05-25 23:36:30',NULL,NULL),(73,6,5,'2003-06-16','2003-06-16','22:16:47','22:16:47','2009-10-17 23:46:36','2009-10-17 23:46:36','n','n'),(74,5,7,'2006-01-18','2006-01-18','06:12:32','06:12:32','2009-02-28 00:00:00','2009-02-28 00:00:00','e','e'),(75,2,0,'1900-01-01','1900-01-01',NULL,NULL,'2004-04-14 13:27:34','2004-04-14 13:27:34','s','s'),(76,4,4,'2000-09-13','2000-09-13','02:38:48','02:38:48','2007-10-20 00:00:00','2007-10-20 00:00:00','w','w'),(77,0,3,'2003-01-26','2003-01-26','00:00:00','00:00:00','2007-11-11 20:10:29','2007-11-11 20:10:29','y','y'),(78,3,1,'2002-09-09','2002-09-09','02:02:49','02:02:49','2001-03-08 00:00:00','2001-03-08 00:00:00','z','z'),(79,0,0,'2001-09-06','2001-09-06','23:43:38','23:43:38','1900-01-01 00:00:00','1900-01-01 00:00:00','b','b'),(80,6,6,'2006-02-23','2006-02-23',NULL,NULL,'2006-07-21 19:26:57','2006-07-21 19:26:57','f','f'),(81,1,2,'2004-06-21','2004-06-21','12:35:01','12:35:01',NULL,NULL,'s','s'),(82,9,NULL,'2006-12-02','2006-12-02','00:18:04','00:18:04',NULL,NULL,'d','d'),(83,4,8,'2005-05-17','2005-05-17','11:53:29','11:53:29','2002-10-24 03:10:15','2002-10-24 03:10:15',NULL,NULL),(84,9,NULL,'2001-12-27','2001-12-27','13:53:19','13:53:19','2002-08-16 00:00:00','2002-08-16 00:00:00','d','d'),(85,4,NULL,'2008-04-11','2008-04-11',NULL,NULL,NULL,NULL,'n','n'),(86,1,NULL,'2000-09-24','2000-09-24','08:53:20','08:53:20','2005-09-09 12:56:20','2005-09-09 12:56:20','i','i'),(87,2,3,'2004-04-20','2004-04-20','10:38:45','10:38:45','2002-06-09 00:00:00','2002-06-09 00:00:00',NULL,NULL),(88,8,7,'2008-07-10','2008-07-10','22:27:36','22:27:36','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h'),(89,1,3,'2007-08-14','2007-08-14','18:06:56','18:06:56','2009-01-21 23:43:07','2009-01-21 23:43:07','d','d'),(90,0,5,'2008-06-08','2008-06-08','03:44:30','03:44:30',NULL,NULL,'c','c'),(91,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-09-06 20:49:33','2007-09-06 20:49:33','i','i'),(92,0,1,'2008-09-17','2008-09-17','22:16:07','22:16:07','2005-09-20 10:11:02','2005-09-20 10:11:02','t','t'),(93,1,2,NULL,NULL,'17:14:13','17:14:13','2007-10-11 00:00:00','2007-10-11 00:00:00','g','g'),(94,2,1,'2004-02-25','2004-02-25','14:49:31','14:49:31','2005-06-13 00:26:39','2005-06-13 00:26:39','q','q'),(95,NULL,7,'2009-11-02','2009-11-02','00:27:36','00:27:36','2001-06-18 08:40:49','2001-06-18 08:40:49','l','l'),(96,3,1,'2001-12-14','2001-12-14','13:59:24','13:59:24','2006-06-07 00:00:00','2006-06-07 00:00:00','n','n'),(97,8,9,NULL,NULL,'01:48:46','01:48:46','2004-03-18 22:35:04','2004-03-18 22:35:04','z','z'),(98,4,9,'1900-01-01','1900-01-01',NULL,NULL,NULL,NULL,'n','n'),(99,4,8,'2002-04-12','2002-04-12','11:07:46','11:07:46','2001-07-15 12:46:49','2001-07-15 12:46:49','r','r'),(100,NULL,3,'2000-02-09','2000-02-09',NULL,NULL,'2001-03-27 06:53:22','2001-03-27 06:53:22','p','p'); ANALYZE TABLE B,C,CC,D; SELECT STRAIGHT_JOIN COUNT(CC_1.c_v_c_key) AS field1 , CC_1.c_date_key AS field2 FROM (CC AS CC_1 INNER JOIN ((C INNER JOIN CC AS CC_2 ON (CC_2.c_v_c_key = C.c_v_c_key))) ON (CC_2.c_v_c_key = C.c_v_c_nokey)) WHERE (CC_2.c_int_key NOT IN ( SELECT SUB_B.pk AS SUBQUERY1_field1 FROM (CC AS SUB_CC_3 STRAIGHT_JOIN B AS SUB_B ON (SUB_B.c_v_c_nokey = SUB_CC_3.c_v_c_key AND SUB_B.pk IN (SELECT CHILD_SUB_D.pk AS CHILD_SUBQUERY1_field1 FROM (CC AS CHILD_SUB_CC RIGHT JOIN D AS CHILD_SUB_D ON (CHILD_SUB_D.c_v_c_nokey = CHILD_SUB_CC.c_v_c_key)) WHERE CHILD_SUB_D.c_v_c_nokey <= 'p'))) WHERE SUB_CC_3.c_v_c_nokey > CC_1.c_v_c_nokey OR SUB_CC_3.c_v_c_key >= SUB_B.c_v_c_nokey)) AND CC_1.c_v_c_key IN ('j') AND CC_1.pk IS NOT NULL GROUP BY field2; Suggested fix: Fix.