将3个查询优化为一个

user3205479 发表于 Dev

用户名

我只是想弄清楚如何将以下查询序列优化为一个查询。

具体而言，以下查询就像用户登录帐户时向用户显示的警报一样。

$sq = "SELECT COUNT(*) as totm FROM login as l 
       JOIN msgs m on m.id = l.id 
       WHERE m.tstamp > l.mtstamp AND l.id = $id;";

$sq .= "SELECT COUNT(*) as totp FROM info as u 
        JOIN pst as p on p.cid =  u.cid
        JOIN login as l on l.id = u.id 
        WHERE p.tstamp > l.ptstamp AND p.id <> u.id 
        AND p.type = 0 AND u.id = $id;";

$sq .= "SELECT COUNT(*) as totq FROM info as u 
        JOIN pst as p on p.cid =  u.cid
        JOIN login as l on l.id = u.id 
        WHERE p.tstamp > l.ptstamp AND p.id <> u.id 
        AND p.type = 1 AND u.id = $id";

现在Iam正在使用它mysqli_multi_query()来运行多个查询。但是我设法将其简化为单个查询

$sq = "SELECT m.totm,p.totp FROM login as l 
       JOIN info as u on u.id = l.id
       LEFT JOIN (SELECT tstamp,id,COUNT(*) as 'totm' FROM msgs GROUP BY id) m 
       ON m.id = l.id AND m.tstamp > l.mtstamp
       LEFT JOIN (SELECT tstamp,cid,type,id,COUNT(*) as 'totp' FROM pst 
                  GROUP BY id) p 
       ON p.cid = u.cid AND p.tstamp > l.ptstamp AND p.type = 0 
       AND p.id <> l.id WHERE l.id = $id 
       LEFT JOIN (SELECT tstamp,cid,type,id,COUNT(*) as 'totp' FROM pst 
                  GROUP BY id) p 
       ON p.cid = u.cid AND p.tstamp > l.ptstamp AND p.type = 1 
       AND p.id <> l.id WHERE l.id = $id LIMIT 1";

当我尝试使用EXPLAIN STATEMENT查询输出的单个查询太糟糕时，很多行都会受到影响。

当我分别对三个查询执行相同的操作时，结果很好。我不确定应该运行多个查询还是尝试单个查询。

谷歌搜索后，我发现groupBY在连接和许多行中引起严重的开销。友善的人让我知道解决这个问题的更好方法。任何人都可以帮助我编写更优化的查询。谢谢。任何帮助是极大的赞赏。

模糊树

如果您要检索特定ID的计数，则建议您将查询作为子查询组合在select中。GROUP BY由于您要查找特定ID的计数，因此没有任何意义。

SELECT
(SELECT COUNT(*) FROM login as l 
       JOIN msgs m on m.id = l.id 
       WHERE m.tstamp > l.mtstamp AND l.id = $id) totm,
(SELECT COUNT(*) FROM info as u 
        JOIN pst as p on p.cid =  u.cid
        JOIN login as l on l.id = u.id 
        WHERE p.tstamp > l.ptstamp AND p.id <> u.id 
        AND p.type = 0 AND u.id = $id) totp,
(SELECT COUNT(*) FROM info as u 
        JOIN pst as p on p.cid =  u.cid
        JOIN login as l on l.id = u.id 
        WHERE p.tstamp > l.ptstamp AND p.id <> u.id 
        AND p.type = 1 AND u.id = $id) totq

将此与获取每个id的计数进行对比，在这种情况下，GROUP BYonid会很有用：

SELECT t1.id,t1.totm,t2.totp,t3.totq
FROM
(SELECT l.id, COUNT(*) as totm FROM login as l 
       JOIN msgs m on m.id = l.id 
       WHERE m.tstamp > l.mtstamp
       GROUP BY l.id) t1
LEFT JOIN (SELECT u.id, COUNT(*) as totp FROM info as u 
        JOIN pst as p on p.cid =  u.cid
        JOIN login as l on l.id = u.id 
        WHERE p.tstamp > l.ptstamp AND p.id <> u.id 
        AND p.type = 0 
        GROUP BY u.id) t2 ON t1.id = t2.id
LEFT JOIN (SELECT u.id, COUNT(*) as totq FROM info as u 
        JOIN pst as p on p.cid =  u.cid
        JOIN login as l on l.id = u.id 
        WHERE p.tstamp > l.ptstamp AND p.id <> u.id 
        AND p.type = 1
        GROUP BY u.id) t3 ON t2.id = t3.id