It seems that the problem is more random and dependent on the server where my forum is hosted; different numbers of post types cause performance problems. I'm testing locally, and for example, this query is very slow:
SELECT `posts`.* FROM `posts` WHERE EXISTS (SELECT 1 FROM `discussions` WHERE `discussions`.`id` = `posts`.`discussion_id` and (`discussions`.`id` not in (SELECT `discussion_id` FROM `discussion_tag` WHERE `tag_id` not in (SELECT `tags`.`id` FROM `tags` WHERE (`tags`.`id` in (SELECT `perm_tags`.`id` FROM `tags` AS `perm_tags`) and (`tags`.`parent_id` in (SELECT `perm_tags`.`id` FROM `tags` AS `perm_tags`) or `tags`.`parent_id` IS NULL))))) and (`discussions`.`is_private` = 0)) and (`posts`.`is_private` = 0) and `posts`.`id` in (5547426, 5547999, 5548004, 5548006, 5548135, 5548566, 5550549, 5572407, 5572479, 5572540, 5572563, 5573148, 5575198, 5577959, 5578047, 5578179, 5578182, 5578271, 5578281, 5578284) and `TYPE` in ('discussionTagged', 'postMoved', 'discussionMerged', 'discussionLocked', 'comment', 'discussionRenamed') LIMIT 21;
But this query is fine after disabling fof/merge-discussions
(no discussionMerged
post type):
SELECT `posts`.* FROM `posts` WHERE EXISTS (SELECT 1 FROM `discussions` WHERE `discussions`.`id` = `posts`.`discussion_id` and (`discussions`.`id` not in (SELECT `discussion_id` FROM `discussion_tag` WHERE `tag_id` not in (SELECT `tags`.`id` FROM `tags` WHERE (`tags`.`id` in (SELECT `perm_tags`.`id` FROM `tags` AS `perm_tags`) and (`tags`.`parent_id` in (SELECT `perm_tags`.`id` FROM `tags` AS `perm_tags`) or `tags`.`parent_id` IS NULL))))) and (`discussions`.`is_private` = 0)) and (`posts`.`is_private` = 0) and `posts`.`id` in (5622685, 5624290, 5624321, 5624556, 5624573, 5624598, 5624794, 5624999, 5625101, 5625378, 5625401, 5626731, 5627112, 5630709, 5630718, 5630721, 5630724, 5630739, 5630773, 5630774) and `TYPE` in ('discussionTagged', 'postMoved', 'discussionLocked', 'comment', 'discussionRenamed') LIMIT 21;
However, when I test in production, both queries are slow. The database version (MariaDB 10.5) is the same on both.
Slow query:
EXPLAIN SELECT `posts`.* FROM `posts` WHERE EXISTS (SELECT 1 FROM `discussions` WHERE `discussions`.`id` = `posts`.`discussion_id` and (`discussions`.`id` not in (SELECT `discussion_id` FROM `discussion_tag` WHERE `tag_id` not in (SELECT `tags`.`id` FROM `tags` WHERE (`tags`.`id` in (SELECT `perm_tags`.`id` FROM `tags` AS `perm_tags`) and (`tags`.`parent_id` in (SELECT `perm_tags`.`id` FROM `tags` AS `perm_tags`) or `tags`.`parent_id` IS NULL))))) and (`discussions`.`is_private` = 0)) and (`posts`.`is_private` = 0) and `posts`.`id` in (5547426, 5547999, 5548004, 5548006, 5548135, 5548566, 5550549, 5572407, 5572479, 5572540, 5572563, 5573148, 5575198, 5577959, 5578047, 5578179, 5578182, 5578271, 5578281, 5578284) and `TYPE` in ('discussionTagged', 'postMoved', 'discussionMerged', 'discussionLocked', 'comment', 'discussionRenamed') LIMIT 21;
1 PRIMARY discussions ALL PRIMARY NULL NULL NULL 316593 Using where
1 PRIMARY posts ref|filter PRIMARY,posts_discussion_id_number_unique,posts_discussion_id_number_index,posts_discussion_id_created_at_index,posts_type_index,posts_type_created_at_index posts_discussion_id_number_unique|posts_type_index 4|407 acp_forum_db.discussions.id 17 (0%) Using index condition; Using where; Using rowid filter
3 MATERIALIZED discussion_tag index PRIMARY discussion_tag_tag_id_foreign 4 NULL 921840 Using where; Using index
4 MATERIALIZED tags index PRIMARY,tags_parent_id_foreign,tags_last_posted_user_id_foreign,tags_last_posted_discussion_id_foreign,tags_tag_group_id_foreign tags_parent_id_foreign 5 NULL 2517 Using where; Using index
4 MATERIALIZED perm_tags eq_ref PRIMARY,tags_parent_id_foreign,tags_last_posted_user_id_foreign,tags_last_posted_discussion_id_foreign,tags_tag_group_id_foreign PRIMARY 4 acp_forum_db.tags.id 1 Using index
6 MATERIALIZED perm_tags index PRIMARY tags_parent_id_foreign 5 NULL 2517 Using index
Fast query:
EXPLAIN SELECT `posts`.* FROM `posts` WHERE EXISTS (SELECT 1 FROM `discussions` WHERE `discussions`.`id` = `posts`.`discussion_id` and (`discussions`.`id` not in (SELECT `discussion_id` FROM `discussion_tag` WHERE `tag_id` not in (SELECT `tags`.`id` FROM `tags` WHERE (`tags`.`id` in (SELECT `perm_tags`.`id` FROM `tags` AS `perm_tags`) and (`tags`.`parent_id` in (SELECT `perm_tags`.`id` FROM `tags` AS `perm_tags`) or `tags`.`parent_id` IS NULL))))) and (`discussions`.`is_private` = 0)) and (`posts`.`is_private` = 0) and `posts`.`id` in (5622685, 5624290, 5624321, 5624556, 5624573, 5624598, 5624794, 5624999, 5625101, 5625378, 5625401, 5626731, 5627112, 5630709, 5630718, 5630721, 5630724, 5630739, 5630773, 5630774) and `TYPE` in ('discussionTagged', 'postMoved', 'discussionLocked', 'comment', 'discussionRenamed') LIMIT 21;
1 PRIMARY posts range PRIMARY,posts_discussion_id_number_unique,posts_discussion_id_number_index,posts_discussion_id_created_at_index,posts_type_index,posts_type_created_at_index PRIMARY 4 NULL 20 Using where
1 PRIMARY discussions eq_ref PRIMARY PRIMARY 4 acp_forum_db.posts.discussion_id 1 Using where
3 DEPENDENT SUBQUERY discussion_tag index_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where
4 DEPENDENT SUBQUERY tags eq_ref PRIMARY,tags_parent_id_foreign,tags_last_posted_user_id_foreign,tags_last_posted_discussion_id_foreign,tags_tag_group_id_foreign PRIMARY 4 func 1 Using where
4 DEPENDENT SUBQUERY perm_tags eq_ref PRIMARY,tags_parent_id_foreign,tags_last_posted_user_id_foreign,tags_last_posted_discussion_id_foreign,tags_tag_group_id_foreign PRIMARY 4 acp_forum_db.tags.id 1 Using index
6 DEPENDENT SUBQUERY perm_tags unique_subquery PRIMARY PRIMARY 4 func 1 Using index