mysql - 查询太长，子查询超过 1 个

Question

我的 mysql 查询有问题:

SELECT
    CONCAT('Kantor ', o.nama) AS nama,
    IF( sj.jenis = 1, CONCAT(sj.sebutan, ' ' , sj.nama), sj.nama) AS sebutan, 
    (
        SELECT
            COUNT(fj.id)
        FROM
            tbl_formasijabatan fj
        WHERE
            fj.sebutanId = sj.id AND
            fj.status    = 1     AND
            fj.orgId     = o.id
    ) AS total

 FROM
     tbl_organisasi o
     RIGHT JOIN tbl_formasijabatan fjj ON fjj.orgId = o.id
     RIGHT JOIN tbl_sebutanjabatan sj  ON sj.id     = fjj.sebutanId
 WHERE
     o.id = 1 AND
     o.unitKerja > 0
 GROUP BY
     o.nama,
     sj.nama

这个查询很长，需要 66 秒才能获取 330 行。

如何将数据检索速度加快到至少 1-10 秒？

您的评论对我很有帮助。

如果需要数据，我将发送数据库。

谢谢

Answer 1

也许您可以将该子查询移动到联接，这应该可以提高性能，尝试如下操作:

<小时/>

SELECT CONCAT('Kantor ', o.nama) AS nama, if(sj.jenis = 1, CONCAT(sj.sebutan, ' ', sj.nama), sj.nama) AS sebutan, X.total 
FROM tbl_organisasi o 
RIGHT JOIN tbl_formasijabatan fjj ON fjj.orgId = o.id 
RIGHT JOIN tbl_sebutanjabatan sj ON sj.id = fjj.sebutanId 
JOIN (SELECT COUNT(fj.id) total, fj.id 
    FROM tbl_formasijabatan fj 
    WHERE fj.sebutanId = sj.id 
    AND fj.status = 1 
    AND fj.orgId = o.id 
    GROUP BY fj.id) X 
ON X.id = fjj.id 
WHERE o.id = 1 AND o.unitKerja > 0 
GROUP BY o.nama , sj.nama

<小时/>

抱歉格式错误，请尝试一下并告诉我。