Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.5, 10.6, 10.11, 11.4
-
None
Description
In sql_mode=oracle queries using SYSDATE instead of NOW() get worse execution plan (not using primary key).
For example partitioned table
CREATE TABLE IF NOT EXISTS `ev` (
|
`ev_id` mediumint(8) unsigned NOT NULL, |
`evc_id` tinyint(3) unsigned NOT NULL, |
`evt_id` mediumint(8) unsigned NOT NULL, |
`evd_id` bigint(20) unsigned DEFAULT NULL, |
`ev_priority` tinyint(3) unsigned NOT NULL, |
`ev_time` datetime NOT NULL,
|
`ev_msg` varchar(250) NOT NULL, |
`mdl_id` smallint(5) unsigned NOT NULL, |
`src_id` int(10) unsigned DEFAULT NULL, |
`ph_id` int(10) unsigned DEFAULT NULL, |
`dp_id` int(10) unsigned DEFAULT NULL, |
`point_id` int(10) unsigned DEFAULT NULL, |
`gr_id` int(10) unsigned DEFAULT NULL, |
`ev_link` varchar(2048) DEFAULT NULL, |
`ds_id` int(10) unsigned DEFAULT NULL, |
`sch_id` mediumint(8) unsigned DEFAULT NULL, |
`das_id` smallint(5) unsigned DEFAULT NULL, |
`ml_id` mediumint(8) unsigned DEFAULT NULL, |
`sheaf_id` mediumint(8) unsigned DEFAULT NULL, |
PRIMARY KEY (`ev_time`,`ev_id`),
|
KEY `ev_dp_i` (`dp_id`,`ev_time`),
|
KEY `ev_ds_i` (`ds_id`,`ev_time`),
|
KEY `ev_evc_i` (`evc_id`,`ev_time`),
|
KEY `ev_evt_i` (`evc_id`,`evt_id`,`ev_time`),
|
KEY `ev_gr_i` (`gr_id`,`ev_time`),
|
KEY `ev_mdl_i` (`mdl_id`,`ev_time`),
|
KEY `ev_ph_i` (`ph_id`,`ev_time`),
|
KEY `ev_point_i` (`point_id`,`ev_time`),
|
KEY `ev_sch_i` (`sch_id`,`ev_time`),
|
KEY `ev_src_i` (`src_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin COMMENT='Sistemos įvykiai' |
PARTITION BY RANGE COLUMNS(`EV_TIME`)
|
(PARTITION `PAR_202139` VALUES LESS THAN ('2021-10-01') ENGINE = InnoDB, |
PARTITION `PAR_202140` VALUES LESS THAN ('2021-10-08') ENGINE = InnoDB, |
PARTITION `PAR_202141` VALUES LESS THAN ('2021-10-15') ENGINE = InnoDB, |
PARTITION `PAR_202142` VALUES LESS THAN ('2021-10-22') ENGINE = InnoDB, |
PARTITION `PAR_202347` VALUES LESS THAN ('2023-11-26') ENGINE = InnoDB, |
PARTITION `PAR_202348` VALUES LESS THAN ('2023-12-03') ENGINE = InnoDB, |
PARTITION `PAR_202349` VALUES LESS THAN ('2023-12-10') ENGINE = InnoDB, |
PARTITION `par_202350` VALUES LESS THAN ('2023-12-17') ENGINE = InnoDB, |
PARTITION `par_202351` VALUES LESS THAN ('2023-12-24') ENGINE = InnoDB, |
PARTITION `par_202352` VALUES LESS THAN ('2023-12-31') ENGINE = InnoDB, |
PARTITION `par_202353` VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB, |
PARTITION `par_202401` VALUES LESS THAN ('2024-01-08') ENGINE = InnoDB, |
PARTITION `par_202402` VALUES LESS THAN ('2024-01-15') ENGINE = InnoDB, |
PARTITION `par_202403` VALUES LESS THAN ('2024-01-22') ENGINE = InnoDB, |
PARTITION `par_202404` VALUES LESS THAN ('2024-01-29') ENGINE = InnoDB, |
PARTITION `par_202405` VALUES LESS THAN ('2024-02-05') ENGINE = InnoDB, |
PARTITION `par_202406` VALUES LESS THAN ('2024-02-12') ENGINE = InnoDB, |
PARTITION `par_202407` VALUES LESS THAN ('2024-02-19') ENGINE = InnoDB, |
PARTITION `par_202408` VALUES LESS THAN ('2024-02-26') ENGINE = InnoDB, |
PARTITION `par_202409` VALUES LESS THAN ('2024-03-04') ENGINE = InnoDB, |
PARTITION `par_202410` VALUES LESS THAN ('2024-03-11') ENGINE = InnoDB, |
PARTITION `par_202411` VALUES LESS THAN ('2024-03-18') ENGINE = InnoDB, |
PARTITION `par_202412` VALUES LESS THAN ('2024-03-25') ENGINE = InnoDB, |
PARTITION `par_202413` VALUES LESS THAN ('2024-04-01') ENGINE = InnoDB, |
PARTITION `par_202414` VALUES LESS THAN ('2024-04-08') ENGINE = InnoDB, |
PARTITION `par_202415` VALUES LESS THAN ('2024-04-15') ENGINE = InnoDB, |
PARTITION `par_202416` VALUES LESS THAN ('2024-04-22') ENGINE = InnoDB, |
PARTITION `par_202417` VALUES LESS THAN ('2024-04-29') ENGINE = InnoDB, |
PARTITION `par_202418` VALUES LESS THAN ('2024-05-06') ENGINE = InnoDB, |
PARTITION `par_202419` VALUES LESS THAN ('2024-05-13') ENGINE = InnoDB, |
PARTITION `par_202420` VALUES LESS THAN ('2024-05-20') ENGINE = InnoDB, |
PARTITION `par_202421` VALUES LESS THAN ('2024-05-27') ENGINE = InnoDB, |
PARTITION `par_202422` VALUES LESS THAN ('2024-06-03') ENGINE = InnoDB, |
PARTITION `par_202423` VALUES LESS THAN ('2024-06-10') ENGINE = InnoDB, |
PARTITION `par_202424` VALUES LESS THAN ('2024-06-17') ENGINE = InnoDB, |
PARTITION `par_202425` VALUES LESS THAN ('2024-06-24') ENGINE = InnoDB, |
PARTITION `par_202426` VALUES LESS THAN ('2024-07-01') ENGINE = InnoDB, |
PARTITION `par_202427` VALUES LESS THAN ('2024-07-08') ENGINE = InnoDB, |
PARTITION `par_202428` VALUES LESS THAN ('2024-07-15') ENGINE = InnoDB, |
PARTITION `par_202429` VALUES LESS THAN ('2024-07-22') ENGINE = InnoDB, |
PARTITION `par_202430` VALUES LESS THAN ('2024-07-29') ENGINE = InnoDB, |
PARTITION `par_202431` VALUES LESS THAN ('2024-08-05') ENGINE = InnoDB, |
PARTITION `par_202432` VALUES LESS THAN ('2024-08-12') ENGINE = InnoDB, |
PARTITION `par_202433` VALUES LESS THAN ('2024-08-19') ENGINE = InnoDB, |
PARTITION `par_202434` VALUES LESS THAN ('2024-08-26') ENGINE = InnoDB, |
PARTITION `par_202435` VALUES LESS THAN ('2024-09-02') ENGINE = InnoDB, |
PARTITION `par_202436` VALUES LESS THAN ('2024-09-09') ENGINE = InnoDB, |
PARTITION `par_202437` VALUES LESS THAN ('2024-09-16') ENGINE = InnoDB, |
PARTITION `par_202438` VALUES LESS THAN ('2024-09-23') ENGINE = InnoDB, |
PARTITION `par_202439` VALUES LESS THAN ('2024-09-30') ENGINE = InnoDB, |
PARTITION `par_202440` VALUES LESS THAN ('2024-10-07') ENGINE = InnoDB, |
PARTITION `par_202441` VALUES LESS THAN ('2024-10-14') ENGINE = InnoDB, |
PARTITION `par_202442` VALUES LESS THAN ('2024-10-21') ENGINE = InnoDB, |
PARTITION `par_202443` VALUES LESS THAN ('2024-10-28') ENGINE = InnoDB, |
PARTITION `par_202444` VALUES LESS THAN ('2024-11-04') ENGINE = InnoDB, |
PARTITION `par_202445` VALUES LESS THAN ('2024-11-11') ENGINE = InnoDB, |
PARTITION `par_202446` VALUES LESS THAN ('2024-11-18') ENGINE = InnoDB, |
PARTITION `par_202447` VALUES LESS THAN ('2024-11-25') ENGINE = InnoDB, |
PARTITION `par_202448` VALUES LESS THAN ('2024-12-02') ENGINE = InnoDB, |
PARTITION `par_202449` VALUES LESS THAN ('2024-12-09') ENGINE = InnoDB, |
PARTITION `par_202450` VALUES LESS THAN ('2024-12-16') ENGINE = InnoDB, |
PARTITION `par_202451` VALUES LESS THAN ('2024-12-23') ENGINE = InnoDB, |
PARTITION `par_202452` VALUES LESS THAN ('2024-12-30') ENGINE = InnoDB, |
PARTITION `par_202453` VALUES LESS THAN ('2025-01-01') ENGINE = InnoDB, |
PARTITION `par_202501` VALUES LESS THAN ('2025-01-08') ENGINE = InnoDB, |
PARTITION `par_202502` VALUES LESS THAN ('2025-01-15') ENGINE = InnoDB, |
PARTITION `par_202503` VALUES LESS THAN ('2025-01-22') ENGINE = InnoDB, |
PARTITION `par_202504` VALUES LESS THAN ('2025-01-29') ENGINE = InnoDB, |
PARTITION `par_202505` VALUES LESS THAN ('2025-02-05') ENGINE = InnoDB, |
PARTITION `par_202506` VALUES LESS THAN ('2025-02-12') ENGINE = InnoDB, |
PARTITION `par_202507` VALUES LESS THAN ('2025-02-19') ENGINE = InnoDB, |
PARTITION `par_202508` VALUES LESS THAN ('2025-02-26') ENGINE = InnoDB, |
PARTITION `par_202509` VALUES LESS THAN ('2025-03-05') ENGINE = InnoDB, |
PARTITION `par_202510` VALUES LESS THAN ('2025-03-12') ENGINE = InnoDB, |
PARTITION `par_202511` VALUES LESS THAN ('2025-03-19') ENGINE = InnoDB); |
|
(5GB size)
on
EXPLAIN SELECT * FROM ev
|
WHERE ev_time> NOW() - INTERVAL 1 minute AND ev_time<= NOW() |
|
id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra
1;SIMPLE;ev;range;PRIMARY;PRIMARY;5;\N;1;Using where
and
on
explain SELECT * FROM ev
|
WHERE ev_time> sysdate - INTERVAL 1 minute AND ev_time<= sysdate |
|
id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra
1;SIMPLE;ev;ALL;\N;\N;\N;\N;21730254;Using where
Version()
11.1.2-MariaDB
it's generally expected, because the value of NOW() does not change for the duration of one query, it's essentially a constant, while SYSDATE can return a different value, if called many times, so it has to be invoked per row and cannot be used for index lookups. Unless you start the server with --sysdate-is-now