'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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|





