我怎样才能简化/改善这个MySQL查询的性能？

我对MySQL非常陌生，并且感谢你们在这个过程中学习了很多东西的同时，在这里我更努力地为之奋斗的更有经验的人提供的大力支持。

我有一个查询，正是我想要的。 但是，它看起来非常混乱，我确信必须有一种方法来简化它。

如何针对性能改进和优化此查询？

非常感谢

            $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."

    FROM $sTable b 
    LEFT JOIN (
   SELECT COUNT(*) AS projects_count, a.songs_id

   FROM $sTable2 a
   GROUP BY a.songs_id
) bb ON bb.songs_id = b.songsID

LEFT JOIN (
   SELECT AVG(rating) AS rating, COUNT(rating) AS ratings_count, c.songid

FROM $sTable3 c

   GROUP BY c.songid   
) bbb ON bbb.songid = b.songsID

LEFT JOIN (
   SELECT c.songid, c.userid,

    CASE WHEN EXISTS 
   ( 
       SELECT songid 
       FROM $sTable3
       WHERE songid = c.songid 
   ) Then 'User Voted'
   else
   (
       'Not Voted'
   )
   end
   AS voted
FROM $sTable3 c
WHERE c.userid = $userid


   GROUP BY c.songid   
) bbbb ON bbbb.songid = b.songsID

编辑：这是一个查询是在做什么的描述： -

我有三个表格：

$ sTable =歌曲表（歌曲，mp3link，艺术品，useruploadid等）

$ sTable2 =有歌曲链接的项目表（projectid，songid，项目名称等）

$ sTable3 =歌曲评分表（歌曲，用户名，评分）

所有这些数据都会输出到JSON数组并显示在我的应用程序的表格中，以提供歌曲列表以及项目和评分数据。

查询本身按照以下顺序执行以下操作： -

收集$ sTable中的所有行

在songsID上加入$ sTable2，并计算此表中具有相同歌曲ID的行数（项目）

在songsID上加入$ stable3，并计算此表中具有相同歌曲ID的列'平均评分'

此时它还计算$ sTable3中具有相同歌曲ID的总行数，以提供总票数。

最后，它会对所有这些行执行检查，以查看$ userid（这是包含登录用户的ID的变量）是否与每行的$ sTable3中的'userid'存储匹配，以检查用户是否已经对给定的歌曲ID进行投票或不投票。 如果匹配，则返回“用户投票”，否则返回“未投票”。 它将它作为一个单独的列输出到我的JSON数组中，然后在我的应用程序中检查客户端并添加一个类。

如果有任何更多的细节需要，请让我知道。 谢谢大家。

编辑：

感谢Aurimis出色的第一次尝试，我正在接近一个更简单的解决方案。

这是我根据该建议尝试的代码。

SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."

    FROM 
      (SELECT 
        $sTable.songsID, COUNT(rating) AS ratings_count, 
        AVG(rating) AS ratings
      FROM $sTable 
        LEFT JOIN $sTable2 ON $sTable.songsID = $sTable2.songs_id
        LEFT JOIN $sTable3 ON $sTable.songsID = $sTable3.songid
      GROUP BY $sTable.songsID) AS A
    LEFT JOIN $sTable3 AS B ON A.songsID = B.songid AND B.userid = $userid

然而，有几个问题。 我不得不删除第一行的答案，因为它导致500内部服务器错误：

IF(B.userid = NULL, "Not voted", "User Voted") AS voted 

显然现在“投票支票”功能丢失了。

此外，更重要的是它不返回在我的数组中定义的所有列，只返回songsID。 我的JSON在'字段列表'中返回未知列'song_name' - 如果我从$ aColumns数组中删除它，它当然会移动到下一个。

我在脚本的开头定义了我的列，因为此数组用于筛选并将JSON编码的输出放在一起。 这是$ aColumns的定义： -

$aColumns = array( 'songsID', 'song_name', 'artist_band_name', 'author', 'song_artwork', 'song_file', 'genre', 'song_description', 'uploaded_time', 'emotion', 'tempo', 'user', 'happiness', 'instruments', 'similar_artists', 'play_count', 'projects_count',  'rating', 'ratings_count', 'voted');

为了快速测试其余的查询，我修改了子查询中的第一行以选择$ sTable。*而不是$ sTable.songsID（请记住$ sTable是歌曲表）

然后......查询显然有效，但当然表现糟糕。 但是，只能从5000首歌曲测试数据集中返回24首歌曲。 因此我将你的第一个'JOIN'改为'LEFT JOIN'，以便所有5000首歌曲都被返回。 为了说明查询需要返回歌曲表中的所有行，但需要为每首歌曲的项目和评分表添加各种额外的数据位。

所以...我们到了那里，我确信这是一个更好的方法，它只是需要一些修改。 感谢你的帮助到目前为止Aurimis。

---

SELECT SQL_CALC_FOUND_ROWS
    songsID, song_name, artist_band_name, author, song_artwork, song_file,
    genre, song_description, uploaded_time, emotion, tempo,
    `user`, happiness, instruments, similar_artists, play_count,
    projects_count,
    rating, ratings_count,
    IF(user_ratings_count, 'User Voted', 'Not Voted') as voted
FROM (
    SELECT
        sp.songsID, projects_count,
        AVG(rating) as rating,
        COUNT(rating) AS ratings_count,
        COUNT(IF(userid=$userid, 1, NULL)) as user_ratings_count
    FROM (
        SELECT songsID, COUNT(*) as projects_count
        FROM $sTable s
        LEFT JOIN $sTable2 p ON s.songsID = p.songs_id
        GROUP BY songsID) as sp
    LEFT JOIN $sTable3 r ON sp.songsID = r.songid
    GROUP BY sp.songsID) as spr
JOIN $sTable s USING (songsID);

您将需要以下索引：

（songs_id）在$ sTable2上

$ sTable3上的复合（songid，rating，userid）

查询背后的想法：

子查询使用INT操作，以便子查询的结果很容易适应内存

左连接分别分组以减少笛卡尔积

用户投票计入与其他评分相同的子查询中，以避免昂贵的相关子查询

所有其他信息将在最终加入时检索

---

让我试着根据你的描述，而不是查询。 为了清楚起见，我将使用Songs来指示Table1 ， Projects以指示Table2和Ratings以指示Table3 。

SELECT 
  /* [column list again] */,
  IF(B.userid = NULL, "Not voted", "Voted") as voted 
FROM 
  (SELECT 
    Songs.SongID, count(rating) as total_votes, 
    avg(rating) as average_rating /*[,.. other columns as you need them] */
  FROM Songs 
    JOIN Projects ON Songs.SongID = Projects.SongID
    LEFT JOIN Ratings ON Songs.SongID = Ratings.SongID
  GROUP BY Songs.SongID) as A
LEFT JOIN Ratings as B ON A.SongID = B.SongID AND B.userid = ? /* your user id */

正如你所看到的，你可以在一个相对简单的查询中获得关于歌曲的所有信息（只需使用Group by和count（）/ avg（）函数）。 要获得某首歌曲是否被特定用户评分的信息，需要一个子查询 - 您可以在其中进行LEFT JOIN，如果用户ID为空 - 则表明他没有投票。

现在，我没有深入查看您的查询，因为它看起来很复杂。 可能是我错过了一些东西 - 如果是这样的话，请更新描述，我可以再试一次:)

链接地址: http://www.djcxy.com/p/56465.html

上一篇: How can I simplify/improve the performance of this MySQL query?

下一篇: accessor not accessible in accept