php - 优化使用三个表对列表进行排序的查询

Question

所以我有一个查询完全按照预期工作，但需要大量优化。我正在使用软件来跟踪网站上的加载时间，网站上 97.8% 的加载时间都是这一功能的结果。因此，在进行查询之前先解释一下我的数据库。

首先我有一个电影表、一个竞赛表和一个投票表。电影可以参加许多竞赛，并且竞赛有很多电影，因为这是多对多的关系，我们有一个数据透视表来显示它们的关系(电影竞赛)，但是在加载竞赛页面时，这些电影需要按投票顺序排列(大多数在顶部投票。至少在底部投票)。

现在，在下面的查询中，您可以看到我正在做什么，从 movieCompetition 表中抓取与当前竞赛 id $competition->id 匹配的电影，然后按该电影的总票数进行排序。就像我说的，这可行，但非常高效，但我想不出另一种方法来做到这一点。

$films =  DB::select( DB::raw("SELECT f.*, COUNT(v.value) AS totalVotes
    FROM filmCompetition AS fc
    JOIN films AS f ON f.id = fc.filmId AND fc.competitionId = '$competition->id'
    LEFT JOIN votes AS v ON f.id = v.filmId AND v.competitionId = '$competition->id'
    GROUP BY f.id
    ORDER BY totalVotes DESC
    ") );

Answer 1

对于此查询，您需要在 filmCompetition(competitionId, filmId)、films(id) 和 votes(filmId, racingId)` 上建立索引。

但是，像这样编写查询可能会更有效:

SELECT f.*,
       (SELECT COUNT(v.value)
        FROM votes v
        WHERE v.filmId = f.id and v.competitionId = '$competition->id'
       ) AS totalVotes
FROM films f
WHERE EXISTS (SELECT 1
              FROM FilmCompetition fc
              WHERE fc.FilmId = f.Filmid AND 
                    fc.competitionId = '$competition->id'
             )
ORDER BY TotalVotes DESC

这节省了外部聚合，这应该是性能上的胜利。对于此版本，索引为 FilmCompetition(FilmId, ComparisonId) 和 Votes(FilmId, ComparisonId)。