Description:
The queries above are completely equivalent and yet they produce different optimizations.
The more advanced intersect optimization is only picked if constant expression simplification takes place. Similar behavior was observed with BKA. This points to an underlying issue with the optimizer which may prevent a wide range of queries from benefitting from better optimizations.
mysql> EXPLAIN SELECT `datetime_key` FROM C WHERE `pk` < 128 AND `int_key` BETWEEN 125 AND (125 + 0) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: C
type: index_merge
possible_keys: PRIMARY,int_key
key: int_key,PRIMARY
key_len: 5,4
ref: NULL
rows: 1
Extra: Using intersect(int_key,PRIMARY); Using where
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT `datetime_key` FROM C WHERE `pk` < 128 AND `int_key` BETWEEN 125 AND 125 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: C
type: ref
possible_keys: PRIMARY,int_key
key: int_key
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
How to repeat:
SET SQL_MODE='NO_ENGINE_SUBSTITUTION';
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) DEFAULT NULL,
`int_key` int(11) DEFAULT NULL,
`date_key` date DEFAULT NULL,
`date_nokey` date DEFAULT NULL,
`time_key` time DEFAULT NULL,
`time_nokey` time DEFAULT NULL,
`datetime_key` datetime DEFAULT NULL,
`datetime_nokey` datetime DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `int_key` (`int_key`),
KEY `date_key` (`date_key`),
KEY `time_key` (`time_key`),
KEY `datetime_key` (`datetime_key`),
KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=InnoDB;
INSERT INTO `C` VALUES (1,NULL,7,'2005-12-13','2005-12-13','20:20:53','20:20:53',NULL,NULL,'l','l'),(2,5,0,'2003-01-01','2003-01-01','13:53:03','13:53:03','2003-09-12 02:19:51','2003-09-12 02:19:51','k','k'),(3,9,2,'2003-03-05','2003-03-05','12:37:08','12:37:08','2006-07-13 12:22:41','2006-07-13 12:22:41',NULL,NULL),(4,3,NULL,'2001-01-25','2001-01-25','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','u','u'),(5,134,122,'2003-03-17','2003-03-17','15:59:14','15:59:14','2000-10-03 15:17:43','2000-10-03 15:17:43','e','e'),(6,NULL,NULL,'2004-09-14','2004-09-14','04:43:51','04:43:51','2009-04-25 16:10:46','2009-04-25 16:10:46','v','v'),(7,1,1,NULL,NULL,'06:49:25','06:49:25','2005-01-11 03:31:23','2005-01-11 03:31:23','i','i'),(8,4,7,'2009-10-17','2009-10-17','15:25:21','15:25:21','2002-12-22 17:39:46','2002-12-22 17:39:46',NULL,NULL),(9,1,2,'2006-11-04','2006-11-04','18:16:04','18:16:04','2007-06-18 19:09:43','2007-06-18 19:09:43','u','u'),(10,0,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2001-06-14 20:33:16','2001-06-14 20:33:16','f','f'),(11,NULL,9,'2007-04-19','2007-04-19','18:36:41','18:36:41','2005-03-06 05:45:38','2005-03-06 05:45:38','u','u'),(12,8,6,'2003-08-18','2003-08-18','18:50:55','18:50:55','2000-10-01 17:07:48','2000-10-01 17:07:48','m','m'),(13,224,127,'2008-06-19','2008-06-19',NULL,NULL,'0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),(14,90,198,'0000-00-00','0000-00-00','08:22:36','08:22:36','2002-02-13 21:59:10','2002-02-13 21:59:10','f','f'),(15,NULL,217,'2004-01-02','2004-01-02',NULL,NULL,'2008-09-21 04:36:56','2008-09-21 04:36:56','v','v'),(16,2,6,'2007-08-12','2007-08-12','09:37:21','09:37:21',NULL,NULL,'j','j'),(17,6,4,'2009-02-28','2009-02-28','03:03:26','03:03:26','2007-04-18 12:09:11','2007-04-18 12:09:11','g','g'),(18,6,8,'2008-08-01','2008-08-01','23:43:45','23:43:45','2001-12-25 21:55:34','2001-12-25 21:55:34','p','p'),(19,160,213,'2002-05-11','2002-05-11','08:19:59','08:19:59','2008-12-21 20:51:04','2008-12-21 20:51:04','u','u'),(20,3,1,NULL,NULL,'15:32:13','15:32:13','2007-05-06 06:09:03','2007-05-06 06:09:03',NULL,NULL);
EXPLAIN SELECT `datetime_key` FROM C WHERE `pk` < 128 AND `int_key` BETWEEN 125 AND (125 + 0)
EXPLAIN SELECT `datetime_key` FROM C WHERE `pk` < 128 AND `int_key`
BETWEEN 125 AND (125);
© 2024, Oracle Corporation and/or its affiliates
Description: The queries above are completely equivalent and yet they produce different optimizations. The more advanced intersect optimization is only picked if constant expression simplification takes place. Similar behavior was observed with BKA. This points to an underlying issue with the optimizer which may prevent a wide range of queries from benefitting from better optimizations. mysql> EXPLAIN SELECT `datetime_key` FROM C WHERE `pk` < 128 AND `int_key` BETWEEN 125 AND (125 + 0) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: C type: index_merge possible_keys: PRIMARY,int_key key: int_key,PRIMARY key_len: 5,4 ref: NULL rows: 1 Extra: Using intersect(int_key,PRIMARY); Using where 1 row in set (0.00 sec) mysql> EXPLAIN SELECT `datetime_key` FROM C WHERE `pk` < 128 AND `int_key` BETWEEN 125 AND 125 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: C type: ref possible_keys: PRIMARY,int_key key: int_key key_len: 5 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) How to repeat: SET SQL_MODE='NO_ENGINE_SUBSTITUTION'; CREATE TABLE `C` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, `date_key` date DEFAULT NULL, `date_nokey` date DEFAULT NULL, `time_key` time DEFAULT NULL, `time_nokey` time DEFAULT NULL, `datetime_key` datetime DEFAULT NULL, `datetime_nokey` datetime DEFAULT NULL, `varchar_key` varchar(1) DEFAULT NULL, `varchar_nokey` varchar(1) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `int_key` (`int_key`), KEY `date_key` (`date_key`), KEY `time_key` (`time_key`), KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`varchar_key`,`int_key`) ) ENGINE=InnoDB; INSERT INTO `C` VALUES (1,NULL,7,'2005-12-13','2005-12-13','20:20:53','20:20:53',NULL,NULL,'l','l'),(2,5,0,'2003-01-01','2003-01-01','13:53:03','13:53:03','2003-09-12 02:19:51','2003-09-12 02:19:51','k','k'),(3,9,2,'2003-03-05','2003-03-05','12:37:08','12:37:08','2006-07-13 12:22:41','2006-07-13 12:22:41',NULL,NULL),(4,3,NULL,'2001-01-25','2001-01-25','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','u','u'),(5,134,122,'2003-03-17','2003-03-17','15:59:14','15:59:14','2000-10-03 15:17:43','2000-10-03 15:17:43','e','e'),(6,NULL,NULL,'2004-09-14','2004-09-14','04:43:51','04:43:51','2009-04-25 16:10:46','2009-04-25 16:10:46','v','v'),(7,1,1,NULL,NULL,'06:49:25','06:49:25','2005-01-11 03:31:23','2005-01-11 03:31:23','i','i'),(8,4,7,'2009-10-17','2009-10-17','15:25:21','15:25:21','2002-12-22 17:39:46','2002-12-22 17:39:46',NULL,NULL),(9,1,2,'2006-11-04','2006-11-04','18:16:04','18:16:04','2007-06-18 19:09:43','2007-06-18 19:09:43','u','u'),(10,0,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2001-06-14 20:33:16','2001-06-14 20:33:16','f','f'),(11,NULL,9,'2007-04-19','2007-04-19','18:36:41','18:36:41','2005-03-06 05:45:38','2005-03-06 05:45:38','u','u'),(12,8,6,'2003-08-18','2003-08-18','18:50:55','18:50:55','2000-10-01 17:07:48','2000-10-01 17:07:48','m','m'),(13,224,127,'2008-06-19','2008-06-19',NULL,NULL,'0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),(14,90,198,'0000-00-00','0000-00-00','08:22:36','08:22:36','2002-02-13 21:59:10','2002-02-13 21:59:10','f','f'),(15,NULL,217,'2004-01-02','2004-01-02',NULL,NULL,'2008-09-21 04:36:56','2008-09-21 04:36:56','v','v'),(16,2,6,'2007-08-12','2007-08-12','09:37:21','09:37:21',NULL,NULL,'j','j'),(17,6,4,'2009-02-28','2009-02-28','03:03:26','03:03:26','2007-04-18 12:09:11','2007-04-18 12:09:11','g','g'),(18,6,8,'2008-08-01','2008-08-01','23:43:45','23:43:45','2001-12-25 21:55:34','2001-12-25 21:55:34','p','p'),(19,160,213,'2002-05-11','2002-05-11','08:19:59','08:19:59','2008-12-21 20:51:04','2008-12-21 20:51:04','u','u'),(20,3,1,NULL,NULL,'15:32:13','15:32:13','2007-05-06 06:09:03','2007-05-06 06:09:03',NULL,NULL); EXPLAIN SELECT `datetime_key` FROM C WHERE `pk` < 128 AND `int_key` BETWEEN 125 AND (125 + 0) EXPLAIN SELECT `datetime_key` FROM C WHERE `pk` < 128 AND `int_key` BETWEEN 125 AND (125);