CODEWITHSUNDEEP
phpmysqlsqleloquentleft-join
CMOS
CMOS

Reputation: 2917

Optimization of query that uses three tables to order a list

So I have a query that works exactly as intended but needs heavy optimization. I am using software to track load times across my site and 97.8% of all load time on my site is a result of this one function. So to explain my database a little bit before getting to the query.

First I have a films table, a competitions table and a votes table. Films can be in many competitions and competitions have many films, since this is a many to many relationship we have a pivot table to show their relationship (filmCompetition) When loading the competition page however these films need to be in order of their votes (most voted at the top. least at the bottom).

Now In the query below you can see what I am doing, grabbing from the films from the filmsCompetition table that match the current competition id $competition->id, and then I order by the total number of votes for that film. Like I said this works but is super efficient but I cannot think of another way to do it.

$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
    ") );

Upvotes: 0

Views: 39

Answers (2)

CMOS
CMOS

Reputation: 2917

The solution I actually ended up using was a little different but since Gordon answered the question I marked him as the correct answer.

My Solution To actually fix this I did a slightly different approach, rather than trying to do all of this in SQL I did my three queries separately and then joined them together in PHP. While this can be slower in my case doing it my original way took about 15 seconds, doing it Gordons way took about 7, and doing it my new way took about 600ms.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

For this query, you want indexes on filmCompetition(competitionId, filmId), films(id), and votes(filmId, competitionId)`.

However, it is probably more efficient to write the query like this:

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

This saves the outer aggregation, which should be a performance win. For this version, the indexes are FilmCompetition(FilmId, CompetitionId) and Votes(FilmId, CompetitionId).

Upvotes: 1

Related Questions