Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.0.0, 11.8.1
-
tested on: WSL(Ubuntu 24.04)+docker(27.4.1), AlmaLinux9
Description
I have a query that works fine on MariaDB 10.4 and later versions of 10.x, but after upgrading to MariaDB 11.4, it has become significantly slower. After some testing, I found that this change occurred starting with MariaDB 11.0.0 or 11.0.1. I tested it on a Dockerized database, and there I found the first version to be 11.0.1, but I assume the issue is already present in 11.0.0 as well. I also tested it on the latest 11.8.1-rc, and the issue still persists.
Query in question:
SELECT videos.v_id as video_id, aux.tags_en as keywords
|
FROM videos
|
LEFT JOIN (
|
select
|
`videos`.`v_id` AS `v_id`,
|
group_concat(`ten`.`t_classic_term` separator ',') AS `tags_en`
|
from (
|
(`videos`
|
left join `content_tags` on(`content_tags`.`c_id` = `videos`.`v_id` and `content_tags`.`ct_tip` = 1))
|
left join `tags` `ten` on(`ten`.`t_id` = `content_tags`.`t_id` and `ten`.`l_id` = 1))
|
where `videos`.`v_enabled` = 1
|
group by `videos`.`v_id`
|
order by `videos`.`v_id`) aux
|
USING(v_id)
|
LIMIT 1;
|
Execution times:
| MariaDB | 10.4 | 11.4 |
|---|---|---|
| time | 0.002 sec | 1 min 1.558 sec |
Analyze output mariadb 10.4.34:
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.8136,
|
"const_condition": "1",
|
"table": {
|
"table_name": "videos",
|
"access_type": "index",
|
"key": "v_is_amateur",
|
"key_length": "1",
|
"used_key_parts": ["v_is_amateur"],
|
"r_loops": 1,
|
"rows": 986272,
|
"r_rows": 1,
|
"r_total_time_ms": 0.4448,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "5",
|
"used_key_parts": ["v_id"],
|
"ref": ["database.videos.v_id"],
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 0,
|
"r_total_time_ms": 0.0031,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"lateral": 1,
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 1,
|
"r_total_time_ms": 0.0382,
|
"table": {
|
"table_name": "videos",
|
"access_type": "eq_ref",
|
"possible_keys": [
|
"PRIMARY",
|
"enabled",
|
"private_pass",
|
"v_enabled",
|
"ind1",
|
"v_enabled_publish_date"
|
],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["v_id"],
|
"ref": ["database.videos.v_id"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0181,
|
"filtered": 50,
|
"r_filtered": 0,
|
"attached_condition": "videos.v_enabled = 1"
|
},
|
"table": {
|
"table_name": "content_tags",
|
"access_type": "ref",
|
"possible_keys": [
|
"content_tag_tip",
|
"c_id",
|
"c_id_t_id_ct_id",
|
"ct_tip_t_id"
|
],
|
"key": "content_tag_tip",
|
"key_length": "4",
|
"used_key_parts": ["c_id"],
|
"ref": ["database.videos.v_id"],
|
"r_loops": 0,
|
"rows": 3,
|
"r_rows": null,
|
"filtered": 50,
|
"r_filtered": null,
|
"attached_condition": "trigcond(content_tags.ct_tip = 1)",
|
"using_index": true
|
},
|
"table": {
|
"table_name": "ten",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "l_id_idx"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["t_id"],
|
"ref": ["database.content_tags.t_id"],
|
"r_loops": 0,
|
"rows": 1,
|
"r_rows": null,
|
"filtered": 50,
|
"r_filtered": null,
|
"attached_condition": "trigcond(ten.l_id = 1 and trigcond(content_tags.t_id is not null))"
|
}
|
}
|
}
|
}
|
}
|
}
|
Analyze output mariadb 11.4.5:
{
|
"query_optimization": {
|
"r_total_time_ms": 0.582299565
|
},
|
"query_block": {
|
"select_id": 1,
|
"cost": 10479.92809,
|
"r_loops": 1,
|
"r_total_time_ms": 64689.98352,
|
"const_condition": "1",
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "videos",
|
"access_type": "index",
|
"key": "v_is_amateur",
|
"key_length": "1",
|
"used_key_parts": ["v_is_amateur"],
|
"loops": 1,
|
"r_loops": 1,
|
"rows": 1000689,
|
"r_rows": 1,
|
"cost": 147.1406155,
|
"r_table_time_ms": 0.013572766,
|
"r_other_time_ms": 0.403535433,
|
"r_engine_stats": {
|
"pages_accessed": 2
|
},
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
}
|
},
|
{
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "5",
|
"used_key_parts": ["v_id"],
|
"ref": ["database.videos.v_id"],
|
"loops": 1000689,
|
"r_loops": 1,
|
"rows": 10,
|
"r_rows": 0,
|
"cost": 10332.78747,
|
"r_table_time_ms": 30153.4874,
|
"r_other_time_ms": 982.731941,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"cost": 0.01280787,
|
"r_loops": 1,
|
"r_total_time_ms": 64689.51214,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "videos",
|
"access_type": "ref",
|
"possible_keys": [
|
"PRIMARY",
|
"enabled",
|
"private_pass",
|
"v_enabled",
|
"ind1",
|
"v_enabled_publish_date"
|
],
|
"key": "v_enabled",
|
"key_length": "2",
|
"used_key_parts": ["v_enabled"],
|
"ref": ["const"],
|
"loops": 1,
|
"r_loops": 1,
|
"rows": 1000689,
|
"r_rows": 508474,
|
"cost": 147.2031447,
|
"r_table_time_ms": 418.8549042,
|
"r_other_time_ms": 483.1884083,
|
"r_engine_stats": {
|
"pages_accessed": 393
|
},
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "videos.v_enabled <=> 1",
|
"using_index": true
|
}
|
},
|
{
|
"table": {
|
"table_name": "content_tags",
|
"access_type": "ref",
|
"possible_keys": [
|
"content_tag_tip",
|
"c_id",
|
"c_id_t_id_ct_id",
|
"ct_tip_t_id"
|
],
|
"key": "content_tag_tip",
|
"key_length": "4",
|
"used_key_parts": ["c_id"],
|
"ref": ["database.videos.v_id"],
|
"loops": 1000689,
|
"r_loops": 508474,
|
"rows": 6,
|
"r_rows": 7.607635789,
|
"cost": 1714.245033,
|
"r_table_time_ms": 6008.807789,
|
"r_other_time_ms": 2998.682801,
|
"r_engine_stats": {
|
"pages_accessed": 1530857
|
},
|
"filtered": 83.33333588,
|
"r_filtered": 86.73344906,
|
"attached_condition": "trigcond(content_tags.ct_tip = 1)",
|
"using_index": true
|
}
|
},
|
{
|
"table": {
|
"table_name": "ten",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "l_id_idx"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["t_id"],
|
"ref": ["database.content_tags.t_id"],
|
"loops": 5003445,
|
"r_loops": 3356066,
|
"r_table_loops": 3348281,
|
"rows": 1,
|
"r_rows": 0.999711269,
|
"cost": 4475.675381,
|
"r_table_time_ms": 18048.67673,
|
"r_other_time_ms": 5595.104881,
|
"r_engine_stats": {
|
"pages_accessed": 7483052,
|
"old_rows_read": 743150
|
},
|
"filtered": 100,
|
"r_filtered": 99.99716849,
|
"attached_condition": "trigcond(ten.l_id = 1 and trigcond(content_tags.t_id is not null))"
|
}
|
}
|
]
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|
SHOW CREATE TABLE videos:
CREATE TABLE `videos` (
|
`v_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
`v_name` varchar(100) NOT NULL,
|
`v_file` char(37) NOT NULL,
|
`v_file_orig` char(37) NOT NULL,
|
`v_filesize` mediumint(8) unsigned DEFAULT 0,
|
`v_filesize_hd` mediumint(8) unsigned DEFAULT 0,
|
`v_filesize_hq` mediumint(8) unsigned DEFAULT 0,
|
`v_filesize_mobile` mediumint(8) unsigned DEFAULT 0,
|
`v_filesize_3gp` mediumint(8) unsigned DEFAULT 0,
|
`v_width` int(4) unsigned DEFAULT 0,
|
`v_height` int(4) unsigned DEFAULT 0,
|
`v_added` int(12) unsigned DEFAULT 0,
|
`v_date_dir` char(7) NOT NULL,
|
`v_encoded_date` int(12) unsigned DEFAULT 0,
|
`v_priority` int(3) unsigned DEFAULT 0,
|
`v_length` int(5) unsigned DEFAULT 0,
|
`v_approved` tinyint(3) unsigned DEFAULT 0,
|
`v_queued` int(10) unsigned NOT NULL DEFAULT 0,
|
`v_encoder` tinyint(3) unsigned DEFAULT 0,
|
`v_enabled` tinyint(1) unsigned DEFAULT 0,
|
`v_enabled_m` tinyint(1) unsigned DEFAULT 0,
|
`v_thumbnail` smallint(5) unsigned DEFAULT 5,
|
`v_casting` tinyint(3) unsigned DEFAULT 0,
|
`v_private` tinyint(1) unsigned DEFAULT 0,
|
`v_pass` char(8) DEFAULT NULL,
|
`v_hash` smallint(5) unsigned DEFAULT NULL,
|
`v_raid_id` smallint(5) unsigned DEFAULT 0,
|
`v_set_id` tinyint(3) unsigned DEFAULT 0,
|
`v_hd_set_id` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`v_hq_set_id` tinyint(3) unsigned NOT NULL DEFAULT 0,
|
`v_mobile_set_id` tinyint(3) unsigned DEFAULT 0,
|
`v_3gp_set_id` tinyint(3) unsigned DEFAULT 0,
|
`v_quality` tinyint(3) unsigned DEFAULT 0,
|
`v_format` tinyint(3) unsigned DEFAULT 0,
|
`v_censored` tinyint(3) unsigned DEFAULT 0,
|
`v_showtitle` tinyint(3) unsigned DEFAULT 0,
|
`v_showtitle_num` tinyint(3) unsigned DEFAULT 0,
|
`v_bitrate` smallint(5) unsigned DEFAULT 0,
|
`v_thumbs_done` tinyint(3) unsigned DEFAULT 0,
|
`v_reviewer_id` int(10) unsigned DEFAULT NULL,
|
`v_review_time` int(10) unsigned DEFAULT NULL,
|
`u_id` int(11) unsigned NOT NULL,
|
`a_id` int(10) unsigned DEFAULT 1,
|
`spw_id` int(10) unsigned DEFAULT 0,
|
`v_count` int(6) unsigned DEFAULT 0,
|
`v_value` int(6) unsigned DEFAULT 0,
|
`v_rating` int(6) unsigned DEFAULT 0,
|
`v_total` int(10) unsigned DEFAULT 0,
|
`v_today` mediumint(8) unsigned DEFAULT 0,
|
`v_yesterday` mediumint(8) unsigned DEFAULT 0,
|
`v_daily` mediumint(8) unsigned DEFAULT 0,
|
`v_lasthour` smallint(5) unsigned DEFAULT 0,
|
`v_current` smallint(5) unsigned DEFAULT 0,
|
`v_dl_pc` mediumint(8) unsigned DEFAULT 0,
|
`v_dl_mo` mediumint(8) unsigned DEFAULT 0,
|
`v_comments_read_time` int(12) unsigned NOT NULL DEFAULT 0,
|
`v_duplicity` int(11) NOT NULL DEFAULT 0,
|
`v_extra_keywords` mediumtext DEFAULT NULL,
|
`v_casting_processed` tinyint(1) NOT NULL DEFAULT 0,
|
`v_last_comment` int(10) unsigned DEFAULT NULL,
|
`v_server_id` varchar(255) NOT NULL,
|
`v_master_video` int(11) unsigned DEFAULT NULL,
|
`v_comments_count` int(10) unsigned NOT NULL DEFAULT 0,
|
`v_comments_last_week` int(10) unsigned NOT NULL DEFAULT 0,
|
`v_best_recent` decimal(10,5) NOT NULL DEFAULT 0.00000,
|
`v_publish_date` int(12) unsigned DEFAULT 0,
|
`v_preview` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`v_preview_moved` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`v_is_amateur` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`v_filesize_4k` mediumint(8) unsigned DEFAULT 0,
|
`v_disabled_comment_section` tinyint(1) NOT NULL DEFAULT 0,
|
`v_disabled_comment_section_count` int(11) NOT NULL DEFAULT 0,
|
PRIMARY KEY (`v_id`),
|
KEY `enabled` (`v_enabled`,`v_length`),
|
KEY `private_pass` (`v_enabled`,`v_private`,`v_pass`),
|
KEY `th_done` (`v_thumbs_done`),
|
KEY `showtitle` (`v_showtitle`,`v_showtitle_num`),
|
KEY `v_encoder` (`v_encoder`),
|
KEY `v_total` (`v_total`),
|
KEY `v_daily` (`v_daily`),
|
KEY `v_yesterday` (`v_yesterday`),
|
KEY `v_today` (`v_today`),
|
KEY `v_rating` (`v_rating`),
|
KEY `v_length` (`v_length`),
|
KEY `v_encoded_date` (`v_encoded_date`),
|
KEY `v_enabled` (`v_enabled`),
|
KEY `v_approved` (`v_approved`),
|
KEY `v_showtitle_num` (`v_showtitle_num`),
|
KEY `v_count` (`v_count`),
|
KEY `v_file` (`v_file`),
|
KEY `v_duplicity` (`v_duplicity`),
|
KEY `v_file_orig` (`v_file_orig`(4)),
|
KEY `v_last_comment` (`v_last_comment`),
|
KEY `v_server_id` (`v_server_id`),
|
KEY `v_master_video` (`v_master_video`),
|
KEY `spw_id_v_enabled_v_approved` (`spw_id`,`v_enabled`,`v_approved`),
|
KEY `v_added` (`v_added`),
|
KEY `ind1` (`v_enabled`,`v_today`),
|
KEY `v_dl_mo` (`v_dl_mo`),
|
KEY `v_dl_pc` (`v_dl_pc`),
|
KEY `v_lasthour` (`v_lasthour`),
|
KEY `v_comments_count` (`v_comments_count`),
|
KEY `v_best_recent` (`v_best_recent`),
|
KEY `v_publish_date` (`v_publish_date`),
|
KEY `v_publish_date_enabled` (`v_publish_date`,`v_enabled`),
|
KEY `v_enabled_publish_date` (`v_enabled`,`v_publish_date`),
|
KEY `idx_quality` (`v_quality`),
|
KEY `v_comments_last_week` (`v_comments_last_week`),
|
KEY `v_is_amateur` (`v_is_amateur`),
|
KEY `fk_u_id_idx` (`u_id`),
|
CONSTRAINT `fk_u_id` FOREIGN KEY (`u_id`) REFERENCES `users` (`u_id`),
|
CONSTRAINT `v_master_video` FOREIGN KEY (`v_master_video`) REFERENCES `videos` (`v_id`) ON DELETE SET NULL ON UPDATE CASCADE
|
) ENGINE=InnoDB AUTO_INCREMENT=1094103 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
SHOW CREATE TABLE content_tags:
CREATE TABLE `content_tags` (
|
`ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`c_id` int(10) unsigned NOT NULL,
|
`t_id` int(10) unsigned NOT NULL,
|
`ct_tip` tinyint(2) unsigned NOT NULL,
|
`ct_like` int(10) unsigned NOT NULL DEFAULT 1,
|
`ct_u_added` int(10) unsigned DEFAULT NULL,
|
`ct_created` int(12) DEFAULT NULL,
|
PRIMARY KEY (`ct_id`),
|
UNIQUE KEY `content_tag_tip` (`c_id`,`t_id`,`ct_tip`) USING BTREE,
|
KEY `t_id_idx` (`t_id`),
|
KEY `c_id` (`c_id`) USING BTREE,
|
KEY `c_id_t_id_ct_id` (`c_id`,`t_id`,`ct_id`),
|
KEY `ct_tip_t_id` (`ct_tip`,`t_id`),
|
CONSTRAINT `t_id` FOREIGN KEY (`t_id`) REFERENCES `tags` (`t_id`) ON DELETE CASCADE ON UPDATE CASCADE
|
) ENGINE=InnoDB AUTO_INCREMENT=34625794 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
SHOW CREATE TABLE tags:
CREATE TABLE `tags` (
|
`t_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`t_term` varchar(255) NOT NULL,
|
`t_classic_term` varchar(255) DEFAULT NULL,
|
`t_created` int(12) unsigned DEFAULT NULL,
|
`t_status` tinyint(2) unsigned DEFAULT NULL,
|
`l_id` int(10) unsigned DEFAULT NULL,
|
`t_description` longtext DEFAULT NULL,
|
`t_img_url` varchar(255) DEFAULT NULL,
|
`t_clicks` int(11) NOT NULL DEFAULT 0,
|
`t_list_in_admin` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`t_keywords` varchar(2048) DEFAULT NULL,
|
`t_meta_description` varchar(255) DEFAULT NULL,
|
`t_h1` text NOT NULL DEFAULT '',
|
`t_title` text NOT NULL DEFAULT '',
|
`t_seo_booster` int(11) NOT NULL DEFAULT 0,
|
PRIMARY KEY (`t_id`),
|
UNIQUE KEY `term_unique` (`t_term`,`l_id`) USING BTREE,
|
KEY `l_id_idx` (`l_id`),
|
KEY `status` (`t_status`) USING BTREE,
|
KEY `status_language` (`t_status`,`l_id`) USING BTREE,
|
CONSTRAINT `l_id` FOREIGN KEY (`l_id`) REFERENCES `languages` (`l_id`) ON DELETE SET NULL ON UPDATE CASCADE
|
) ENGINE=InnoDB AUTO_INCREMENT=80939 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
Just to clarify, the aux subquery in the query above is actually a view in the real application. However, whether the query uses the subquery directly or replaces it with the view, the performance is the same.
SHOW CREATE VIEW sphinx_video_tags_en:
CREATE ALGORITHM = UNDEFINED VIEW `sphinx_video_tags_en` AS
|
(
|
select `videos`.`v_id` AS `v_id`, group_concat(`ten`.`t_classic_term` separator ',') AS `tags_en`
|
from ((`videos` left join `content_tags`
|
on (`content_tags`.`c_id` = `videos`.`v_id` and `content_tags`.`ct_tip` = 1)) left join `tags` `ten`
|
on (`ten`.`t_id` = `content_tags`.`t_id` and `ten`.`l_id` = 1))
|
where `videos`.`v_enabled` = 1
|
group by `videos`.`v_id`
|
order by `videos`.`v_id`)
|