Search code examples
mysqlsqlquery-optimizationaggregate-functionsouter-join

LEFT JOIN query with COUNT takes so long to execute

I have 4 tables, album, artist, song_cover and song. Am trying to join the 3 tables to the album table and include the total number of songs within each album.

The query I have so far returns results as expected but takes almost over a minute to execute.

SELECT frco_album.*,
COUNT(frco_song.song_id) AS TotalSongs, 
artist_aka, artist_address, 
cover_filename
FROM frco_album
LEFT JOIN frco_song ON frco_album.album_id = 
frco_song.song_album_id
LEFT JOIN frco_artist ON frco_album.album_artist = 
frco_artist.artist_id
LEFT JOIN frco_song_cover ON frco_album.album_cover_id = 
frco_song_cover.cover_id
GROUP BY frco_album.album_id
ORDER BY album_month DESC LIMIT 0, 20;

When I get rid of the song table LEFT JOIN song ON album.album_id = song.song_album_id and COUNT(song.song_id) AS TotalSongs, the query executes fast, as expected.

What am I doing wrong here?

EDIT: I've edited the question to include the tables and changed the query to reflect the actual rows in the tables.

song_cover table

song table

artist table

enter image description here

enter image description here

Solution

  • The left join will multiply rows and then you condense them back using group by. Assuming that there is one artist and cover per album I would try counting the songs inside the select clause:

    SELECT album.*, artist_aka, artist_address, cover_filename, (
    SELECT COUNT(*)
    FROM songs
    WHERE song.song_album_id = album.album_id
) AS TotalSongs
FROM album
LEFT JOIN artist ON album.album_artist_id = artist.artist_id
LEFT JOIN song_cover ON album.album_cover_id = song_cover.cover_id
ORDER BY album_plays DESC
LIMIT 0, 20