I have found some solutions to this from other sources but none of them seem to be executing efficiently for me. I am using derby and my current solution is taking over a minute to execute!
I am trying to find the number of songs and albums that belong to a given artist and display those numbers in 2 separate columns next to the artist's name and id. ex:
ID Name Songs Albums
425 J. Smith 0 0
314 A. Payne 32 3
412 K. Thomas 423 35
The artist table has artist_id, the song table has song_id and album_id, and the album table has album_id and artist_id. The tables aren't tiny. Artist has about 1,100 records, song has about 73,000, and album has about 7,000.
Here is my current solution:
select ar.artist_id, ar.artist_name, count(s.song_id), count(distinct(al.album_id))
from artist ar left outer join
(album al inner join song s
on al.album_id = s.album_id)
on ar.artist_id = al.artist_id
group by ar.artist_id, ar.artist_name
Is there a way to make this query perform better? The ID columns are all primary keys in their respective tables, so they should already by indexed in derby if I understand correctly.
This query uses derived tables to get the song and album counts
select ar.artist_id, ar.artist_name,
coalesce(t1.song_cnt,0), coalesce(t2.album_cnt,0)
from artist ar left join (
select artist_id, count(*) song_cnt
from song group by artist_id
) t1 on t1.artist_id = ar.artist_id
left join (
select artist_id, count(*) album_cnt
from album group by artist_id
) t2 on t2.artist_id = ar.artist_id
You might want to look into storing the counts themselves in your db and updating them using triggers.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments