How can I more efficiently execute a query that returns how many times the identifier appears in two other tables?

Question

I found some solutions for this from other sources, but none of them seem to work for me efficiently. I use derby and my current solution takes a minute to execute!

I am trying to find the number of songs and albums owned by this artist and display them in two separate columns next to the name and identifier of the artist. 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 are not small. The artist has about 1,100 records, the song has about 73,000, and the 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

? , , .

Answer 1

,

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

, db .

Answer 2

, , . MySQL... , , , , , .

(SELECT artist_id, COUNT (id) AS numAlbums FROM album GROUP BY artist_id) (SELECT album_id, COUNT (id) AS numSongs FROM song GROUP BY album_id)

... , , , , , . , , (, ) .