'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



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source