sql - How can I more efficiently perform a query that returns how many times an ID appears in two other tables? -

i have found solutions other sources none of them seem executing efficiently me. using derby , current solution taking on minute execute!

i trying find number of songs , albums belong given artist , display numbers in 2 separate columns next artist's name , 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, song table has song_id , album_id, , album table has album_id , artist_id. tables aren't tiny. artist has 1,100 records, song has 73,000, , album has 7,000.

here current solution:

select ar.artist_id, ar.artist_name, count(s.song_id), count(distinct(al.album_id)) 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 ar.artist_id, ar.artist_name 

is there way make query perform better? id columns primary keys in respective tables, should indexed in derby if understand correctly.

this query uses derived tables song , album counts

select ar.artist_id, ar.artist_name,      coalesce(t1.song_cnt,0), coalesce(t2.album_cnt,0) artist ar left join (     select artist_id, count(*) song_cnt     song group artist_id ) t1 on t1.artist_id = ar.artist_id  left join (    select artist_id, count(*) album_cnt    album group artist_id ) t2 on t2.artist_id = ar.artist_id 

you might want storing counts in db , updating them using triggers.