Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3
-
None
Description
I'm opening the issue as requested by Igor in MDEV-17795
In 10.3 semi-join optimisation could be make queries much slower:
SELECT `news`.* FROM `news` WHERE (news.publier = 1) AND (news.date_publication < "2019-03-07 19:20:08") AND (news.date_fin > "2019-03-07 19:20:08" OR news.date_fin IS NULL) AND (news.sponsored = 0) AND (news.idnews in ([about 1600 ids]) ) GROUP BY `news`.`idnews` ORDER BY `date_publication` desc LIMIT 5 |
Execution plan:
*************************** 1. row ***************************
|
id: 1
|
select_type: PRIMARY
|
table: <derived3>
|
type: ALL
|
possible_keys: NULL
|
key: NULL
|
key_len: NULL
|
ref: NULL
|
rows: 8949
|
Extra: Start temporary; Using temporary; Using filesort
|
*************************** 2. row ***************************
|
id: 1
|
select_type: PRIMARY
|
table: news
|
type: eq_ref
|
possible_keys: PRIMARY,date_publication
|
key: PRIMARY
|
key_len: 4
|
ref: tvc_0._col_1
|
rows: 1
|
Extra: Using where; End temporary
|
*************************** 3. row ***************************
|
id: 3
|
select_type: DERIVED
|
table: NULL
|
type: NULL
|
possible_keys: NULL
|
key: NULL
|
key_len: NULL
|
ref: NULL
|
rows: NULL
|
Extra: No tables used
|
3 rows in set (0.006 sec)
|
Table struct:
CREATE TABLE `news` ( |
`idnews` int(11) NOT NULL AUTO_INCREMENT, |
`titre` varchar(255) CHARACTER SET latin1 DEFAULT NULL, |
`contenu` text CHARACTER SET latin1 DEFAULT NULL, |
`date_publication` timestamp NULL DEFAULT '0000-00-00 00:00:00', |
`date_modification` timestamp NULL DEFAULT '0000-00-00 00:00:00', |
`image` varchar(255) CHARACTER SET latin1 DEFAULT NULL, |
`image_mini` varchar(255) CHARACTER SET latin1 DEFAULT NULL, |
`publier` tinyint(1) DEFAULT NULL, |
`categorie` int(11) DEFAULT NULL, |
`likes` int(11) NOT NULL DEFAULT 0, |
`visites` int(11) NOT NULL DEFAULT 0, |
`retweet` int(11) NOT NULL DEFAULT 0, |
`gplus` int(11) NOT NULL DEFAULT 0, |
`maj_social` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', |
`date_fin` timestamp NULL DEFAULT NULL, |
`chapo` text DEFAULT NULL, |
`star_news` tinyint(1) DEFAULT 0, |
`auteur` varchar(255) DEFAULT NULL, |
`newsimport` int(10) NOT NULL, |
`old` varchar(200) NOT NULL, |
`site_origine` varchar(25) NOT NULL, |
`id_origine` int(6) NOT NULL, |
`credits_photo` varchar(255) DEFAULT NULL, |
`iframe_videos` text DEFAULT NULL, |
`zones` varchar(200) DEFAULT NULL, |
`date_affichage` tinyint(3) unsigned DEFAULT 1, |
`partage_affichage` tinyint(3) unsigned DEFAULT 1, |
`sponsored` tinyint(3) unsigned DEFAULT 0, |
`legende_photo` varchar(255) DEFAULT NULL, |
`ordre` int(11) DEFAULT NULL, |
`contenu_amp` text DEFAULT NULL, |
`iframe_videos_amp` text DEFAULT NULL, |
PRIMARY KEY (`idnews`), |
KEY `newsimport` (`newsimport`,`idnews`), |
KEY `date_publication` (`date_publication`) |
) ENGINE=InnoDB AUTO_INCREMENT=41981 DEFAULT CHARSET=utf8 |
Rewriting by the optimiser:
/* select#1 */ select `voltage`.`news`.`idnews` AS `idnews`,`voltage`.`news`.`titre` AS `titre`,`voltage`.`news`.`contenu` AS `contenu`,`voltage`.`news`.`date_publication` AS `date_publication`,`voltage`.`news`.`date_modification` AS `date_modification`,`voltage`.`news`.`image` AS `image`,`voltage`.`news`.`image_mini` AS `image_mini`,`voltage`.`news`.`publier` AS `publier`,`voltage`.`news`.`categorie` AS `categorie`,`voltage`.`news`.`likes` AS `likes`,`voltage`.`news`.`visites` AS `visites`,`voltage`.`news`.`retweet` AS `retweet`,`voltage`.`news`.`gplus` AS `gplus`,`voltage`.`news`.`maj_social` AS `maj_social`,`voltage`.`news`.`date_fin` AS `date_fin`,`voltage`.`news`.`chapo` AS `chapo`,`voltage`.`news`.`star_news` AS `star_news`,`voltage`.`news`.`auteur` AS `auteur`,`voltage`.`news`.`newsimport` AS `newsimport`,`voltage`.`news`.`old` AS `old`,`voltage`.`news`.`site_origine` AS `site_origine`,`voltage`.`news`.`id_origine` AS `id_origine`,`voltage`.`news`.`credits_photo` AS `credits_photo`,`voltage`.`news`.`iframe_videos` AS `iframe_videos`,`voltage`.`news`.`zones` AS `zones`,`voltage`.`news`.`date_affichage` AS `date_affichage`,`voltage`.`news`.`partage_affichage` AS `partage_affichage`,`voltage`.`news`.`sponsored` AS `sponsored`,`voltage`.`news`.`legende_photo` AS `legende_photo`,`voltage`.`news`.`ordre` AS `ordre`,`voltage`.`news`.`contenu_amp` AS `contenu_amp`,`voltage`.`news`.`iframe_videos_amp` AS `iframe_videos_amp` from `voltage`.`news` semi join ((values [...] `tvc_0`) where `voltage`.`news`.`publier` = 1 and `voltage`.`news`.`sponsored` = 0 and `voltage`.`news`.`date_publication` < '2019-03-07 19:20:08' and (`voltage`.`news`.`date_fin` > '2019-03-07 19:20:08' or `voltage`.`news`.`date_fin` is null) and `voltage`.`news`.`idnews` = `tvc_0`.`_col_1` group by `voltage`.`news`.`idnews` order by `voltage`.`news`.`date_publication` desc limit 5 |
The associated profiling:
SHOW PROFILE FOR QUERY 1;
|
+--------------------------------+----------+
|
| Status | Duration |
|
+--------------------------------+----------+
|
| Starting | 0.000194 |
|
| Waiting for query cache lock | 0.000032 |
|
| Init | 0.000022 |
|
| Checking query cache for query | 0.003343 |
|
| Checking permissions | 0.000030 |
|
| Opening tables | 0.000044 |
|
| After opening tables | 0.000027 |
|
| System lock | 0.000024 |
|
| Table lock | 0.000027 |
|
| Waiting for query cache lock | 0.000431 |
|
| Init | 0.001572 |
|
| Optimizing | 0.000904 |
|
| Statistics | 0.000100 |
|
| Preparing | 0.000069 |
|
| Creating tmp table | 0.000072 |
|
| Sorting result | 0.000047 |
|
| Executing | 0.000024 |
|
| Sending data | 0.001152 |
|
| Removing tmp table | 0.000029 |
|
| Sending data | 0.107823 |
|
| Creating sort index | 0.012458 |
|
| Removing tmp table | 0.006703 |
|
| Creating sort index | 0.000047 |
|
| End of update loop | 0.000027 |
|
| Removing tmp table | 0.000027 |
|
| End of update loop | 0.000031 |
|
| Query end | 0.000023 |
|
| Commit | 0.000022 |
|
| Closing tables | 0.000021 |
|
| Removing tmp table | 0.000023 |
|
| Closing tables | 0.000021 |
|
| Unlocking tables | 0.000020 |
|
| Closing tables | 0.000030 |
|
| Starting cleanup | 0.000021 |
|
| Freeing items | 0.000256 |
|
| Updating status | 0.000030 |
|
| Waiting for query cache lock | 0.000020 |
|
| Updating status | 0.000071 |
|
| Waiting for query cache lock | 0.000026 |
|
| Updating status | 0.000026 |
|
| Storing result in query cache | 0.000035 |
|
| Reset for next command | 0.000030 |
|
+--------------------------------+----------+
|
42 rows in set (0.000 sec)
|
Output of the Analyze format:
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 125.17,
|
"filesort": {
|
"sort_key": "news.date_publication desc",
|
"r_loops": 1,
|
"r_total_time_ms": 11.992,
|
"r_limit": 5,
|
"r_used_priority_queue": true,
|
"r_output_rows": 6,
|
"temporary_table": {
|
"duplicates_removal": {
|
"table": {
|
"table_name": "<derived3>",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 8949,
|
"r_rows": 8949,
|
"r_total_time_ms": 0.5357,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"query_block": {
|
"union_result": {
|
"table_name": "<unit3>",
|
"access_type": "ALL",
|
"r_loops": 0,
|
"r_rows": null,
|
"query_specifications": [
|
{
|
"query_block": {
|
"select_id": 3,
|
"table": {
|
"message": "No tables used"
|
}
|
}
|
}
|
]
|
}
|
}
|
}
|
},
|
"table": {
|
"table_name": "news",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "date_publication"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["idnews"],
|
"ref": ["tvc_0._col_1"],
|
"r_loops": 8949,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 28.308,
|
"filtered": 100,
|
"r_filtered": 99.497,
|
"attached_condition": "news.publier = 1 and news.sponsored = 0 and news.date_publication < '2019-03-07 19:20:08' and (news.date_fin > '2019-03-07 19:20:08' or news.date_fin is null) and news.idnews = tvc_0._col_1"
|
}
|
}
|
}
|
}
|
}
|
}
|
In 10.2 :
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.1169,
|
"table": {
|
"table_name": "news",
|
"access_type": "index",
|
"possible_keys": ["PRIMARY", "date_publication"],
|
"key": "date_publication",
|
"key_length": "5",
|
"used_key_parts": ["date_publication"],
|
"r_loops": 1,
|
"rows": 6626,
|
"r_rows": 5,
|
"r_total_time_ms": 0.0761,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "news.publier = 1 and news.sponsored = 0 and news.date_publication < '2019-03-07 19:20:08' and (news.date_fin > '2019-03-07 19:20:08' or news.date_fin is null) and news.idnews in ([...])"
|
}
|
}
|
}
|
Explain is trivial:
+------+-------------+-------+-------+--------------------------+------------------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+--------------------------+------------------+---------+------+------+-------------+
|
| 1 | SIMPLE | news | index | PRIMARY,date_publication | date_publication | 5 | NULL | 5 | Using where |
|
+------+-------------+-------+-------+--------------------------+------------------+---------+------+------+-------------+
|
In 10.3, the query execution takes about 0.18sec vs 0.06 sec in 10.2