在MariaDB 10.7.6上使用具有许多子选项的查询时性能缓慢

Question

在通过自制软件安装了MariaDB 10.7的M1 Max macOS 12.6上运行。

我很难找出这比我预期的要慢的原因。我预计会有2到10分钟，但我看到的时间超过了一个小时。有5700万条记录，似乎在扫描5500万条。在底部，我列出了每个查询和计数。我怀疑使用临时；使用文件排序。我已经为大多数引用列添加了索引。

我正在寻找更多的想法来解决这个问题。如果这是我能做的最好的事情，那也是一个有效的结果。我也在考虑迁移到全文搜索，但我还没有准备好。

以表格格式分析结果：

表>可能的_keys/th>>r_rows<1th>筛选r_filtered<2th>额外RPost>>><10.00>>0.02RPost><1>RPost><1><20.00>>10.00eq_ref主测试。R主Id<1><10.00>参考History_idx_10.78使用其中测试。RPost.Id<1><2.61><10>>使用其中未使用表

id	select_type	类型	key_len	ref	行
1	PRIMARY	index_merge	RPost_idx_6、RPost_idx_7、RPost_idx_8	RPost_id_x6、rpest_idx_7、rpest_idx_8	6,6	NULL	6176002	55104309.00	使用intersect(RPost_idx_6，RPost_idx_7，RPost_idx_8(；使用where；使用临时；使用文件排序
11	依赖子查询	eq_ref	PRIMARY				测试。RPost.Id	<1.00>	100.00	100.00
10	依赖子查询	eq_ref	PRIMARY				测试。RPost.Id	<100>
9	依赖子查询	R主	主		4		1.00	100.00
	依赖子查询	历史	posthistory_idx_1、History_idx_2、History_idx_4	4	测试。RPost.Id	1	2.61	2.98
	依赖子查询	历史	参考	posthistory_idx_1、History_Id x_2、History_idx_4	History_idx_1	4	96.34
	依赖子查询
12	DERIVED	NULL	NULL[/td>	NULL	NULL
13	UNION	NULL	NULL	NULL[/td>	NULL	NULL	NULL		NULL
NULL	UNION RESULT	<联合12，13>	ALL	NULL	NULL
5	依赖子查询	所有	NULL	NULL
2	DERIVED	NULL	NULL[/td>	NULL	NULL
3	UNION	NULL	NULL[/td>	NULL	NULL
NULL	UNION RESULT	<union2，3>	ALL	NULL	NULL

考虑在没有嵌套子选择的情况下重构查询以进行条件聚合：

WITH Common AS (
SELECT '% strings %' AS Value
UNION ALL
SELECT '% tofind %'
),
MyRPost (Id, STitle, SBody, Queued, Frozen) AS (
SELECT
r.Id,
COUNT(ct.Value) AS STitle,
COUNT(cb.Value) AS SBody,
SUM(h.TypeId BETWEEN 1 AND 9) % 2 AS Queued,
SUM(h.TypeId BETWEEN 10 AND 19) % 2 AS Frozen
FROM RPost r
LEFT JOIN Common ct ON r.Title LIKE ct.Value
LEFT JOIN Common cb ON r.Body LIKE cb.Value
LEFT JOIN History h ON h.PostId = r.Id
WHERE r.CompletedDate IS NULL
AND r.CancelDate is NULL
AND r.PausedDate IS NULL
GROUP BY r.Id
HAVING (Queued + Frozen) = 0
AND (STitle + SBody) > 1
)
SELECT 
m.STitle, m.SBody, m.Id, m.Queued, m.Frozen,
r.ParentId AS OriginId, r.PostTypeId AS Type, r.Title
FROM MyRPost m
LEFT JOIN RPost r ON m.Id = r.Id
ORDER BY (m.STitle + m.SBody), m.Id