LEFT JOIN query with COUNT takes so long to execute-CodePudding

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.

CodePudding user response：

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