MySQL子查询效率

Question

我有这个查询，它增加了一个'stats'表的值的负载。当查询运行时，它会选择这些值来填充子查询中的表。我想知道是否可以更有效地做出这种做法，或者如果我正在做一些非常错误的事情。我不是那么熟悉MySQL的所以任何帮助将是巨大的:)

下面是该查询：

UPDATE mediastats SET 
    mediastats_members = (SELECT count(*) FROM status WHERE status_media_id = :id), 
    mediastats_avscore = (SELECT AVG(status_rating) FROM status WHERE status_media_id = :id), 
    mediastats_done = (SELECT count(*) FROM status WHERE status_status = 'done' AND status_media_id = :id), 
    mediastats_doing = (SELECT count(*) FROM status WHERE status_status = 'doing' AND status_media_id = :id), 
    mediastats_redoing = (SELECT count(*) FROM status WHERE status_status = 'redoing' AND status_media_id = :id), 
    mediastats_dropped = (SELECT count(*) FROM status WHERE status_status = 'dropped' AND status_media_id = :id), 
    mediastats_wantto = (SELECT count(*) FROM status WHERE status_status = 'wantto' AND status_media_id = :id), 
    mediastats_wont = (SELECT count(*) FROM status WHERE status_status = 'wont' AND status_media_id = :id), 
    mediastats_stalled = (SELECT count(*) FROM status WHERE status_status = 'stalled' AND status_media_id = :id), 
    mediastats_rating_1 = (SELECT count(*) FROM status WHERE status_rating = 1 AND status_media_id = :id), 
    mediastats_rating_2 = (SELECT count(*) FROM status WHERE status_rating = 2 AND status_media_id = :id), 
    mediastats_rating_3 = (SELECT count(*) FROM status WHERE status_rating = 3 AND status_media_id = :id), 
    mediastats_rating_4 = (SELECT count(*) FROM status WHERE status_rating = 4 AND status_media_id = :id), 
    mediastats_rating_5 = (SELECT count(*) FROM status WHERE status_rating = 5 AND status_media_id = :id), 
    mediastats_rating_6 = (SELECT count(*) FROM status WHERE status_rating = 6 AND status_media_id = :id), 
    mediastats_rating_7 = (SELECT count(*) FROM status WHERE status_rating = 7 AND status_media_id = :id), 
    mediastats_rating_8 = (SELECT count(*) FROM status WHERE status_rating = 8 AND status_media_id = :id), 
    mediastats_rating_9 = (SELECT count(*) FROM status WHERE status_rating = 9 AND status_media_id = :id), 
    mediastats_rating_10 = (SELECT count(*) FROM status WHERE status_rating = 10 AND status_media_id = :id) 
    WHERE mediastats_media_id = :id 

的：ID是从PHP加入。

Answer 1

这里是我如何与PDO做它在PHP：

$sql = " 
    SELECT 
    COUNT(*) AS mediastats_members, 
    AVG(status_rating) AS mediastats_avscore, 
    SUM(status_status = 'done') AS mediastats_done, 
    SUM(status_status = 'doing') AS mediastats_doing, 
    SUM(status_status = 'redoing') AS mediastats_redoing, 
    SUM(status_status = 'dropped') AS mediastats_dropped, 
    SUM(status_status = 'wantto') AS mediastats_wantto, 
    SUM(status_status = 'wont') AS mediastats_wont, 
    SUM(status_status = 'stalled') AS mediastats_stalled, 
    SUM(status_rating = 1) AS mediastats_rating_1, 
    SUM(status_rating = 2) AS mediastats_rating_2, 
    SUM(status_rating = 3) AS mediastats_rating_3, 
    SUM(status_rating = 4) AS mediastats_rating_4, 
    SUM(status_rating = 5) AS mediastats_rating_5, 
    SUM(status_rating = 6) AS mediastats_rating_6, 
    SUM(status_rating = 7) AS mediastats_rating_7, 
    SUM(status_rating = 8) AS mediastats_rating_8, 
    SUM(status_rating = 9) AS mediastats_rating_9, 
    SUM(status_rating = 10) AS mediastats_rating_10 
    FROM status 
    WHERE status_media_id = :id"; 
$stmt = $pdo->prepare($sql); 
$stmt->execute(array("id"=>$id)); 
$params = $stmt->fetch(PDO::FETCH_ASSOC); 

这样你计算出表的一个通所有的集合体，而是采用了独立的子查询每个计数。

我正在使用MySQL的技巧 - 布尔表达式的SUM（）等于表达式为真的COUNT（）。这是因为MySQL布尔表达式总是返回0或1，并且0和1的和等于1的COUNT。

然后你可以从上面的查询作为参数数组的UPDATE语句中使用结果：

$sql = " 
    UPDATE mediastats SET 
     mediastats_members = :mediastats_members, 
     mediastats_avscore = :mediastats_avscore, 
     mediastats_done = :mediastats_done, 
     mediastats_doing = :mediastats_doing, 
     mediastats_redoing = :mediastats_redoing, 
     mediastats_dropped = :mediastats_dropped, 
     mediastats_wantto = :mediastats_wantto, 
     mediastats_wont = :mediastats_wont, 
     mediastats_stalled = :mediastats_stalled, 
     mediastats_rating_1 = :mediastats_rating_1, 
     mediastats_rating_2 = :mediastats_rating_2, 
     mediastats_rating_3 = :mediastats_rating_3, 
     mediastats_rating_4 = :mediastats_rating_4, 
     mediastats_rating_5 = :mediastats_rating_5, 
     mediastats_rating_6 = :mediastats_rating_6, 
     mediastats_rating_7 = :mediastats_rating_7, 
     mediastats_rating_8 = :mediastats_rating_8, 
     mediastats_rating_9 = :mediastats_rating_9, 
     mediastats_rating_10 = :mediastats_rating_10 
    WHERE mediastats_media_id = :id"; 

$stmt = $pdo->prepare($sql); 
$params["id"] = $id; 
$stmt->execute($params); 

自PHP 5.3.4，PDO接受参数数组键没有前导:。当您在查询中声明参数占位符时，您需要冒号，但您在提供给execute（）的值的数组中不需要冒号。

Answer 2

UPDATE (
     SELECT status_media_id, 
       COUNT(*) AS cnt, AVG(status_rating) AS avg_rating, 
       SUM(status_status = 'done') AS cnt_done, 
       ... 
     FROM status 
     WHERE status_media_id = :id 
     ) s 
JOIN mediastats ms 
ON  ms.mediastats_media_id = s.status_media_id 
SET  ms.mediastats_members = cnt, 
     ms.mediastats_avscore = avg_rating, 
     ms.mediastats_done = cnt_done, 
     ...