查询 JSON 对象时 MySQL 查询超时

Question

以下查询似乎生成了一个无限循环，因为它在 30 秒后超时。在 C++ 中运行它永远不会返回到代码。

SELECT author_obj->>'$.name' FROM author WHERE author_obj->'$.name' NOT IN 
    (SELECT DISTINCT author_obj->'$.name' 
    FROM response INNER JOIN author ON response.author_id = author.id 
    WHERE response.parent IS NOT NULL
    GROUP BY author.id);

内部部分按预期运行并返回数据库中在 1.5 秒内回复了其他帖子的作者。

查询的目标是找到所有只发消息但从不回复其他用户的用户，这些用户被保存为 JSON 对象。

Answer 1

SELECT author_obj->>'$.name'
FROM author 
WHERE author_obj->'$.name' NOT IN 
    (SELECT DISTINCT author_obj->'$.name' 
    FROM response
    INNER JOIN author ON response.author_id = author.id 
    WHERE response.parent IS NOT NULL
    GROUP BY author.id)

可能这样会更快:

SELECT a1.author_obj->>'$.name'
FROM author a1
WHERE NOT EXISTS
    (SELECT 1 FROM
    FROM response r
    INNER JOIN author a2 ON r.author_id = a2.id 
    WHERE a2.author_obj->'$.name' = a1.author_obj->'$.name'
      AND r.parent IS NOT NULL)

(警告:我可能没有正确重构它。)