optimization - speed up mysql query with multiple subqueries -

i'm wondering if there's way speed mysql query ordered multiple subqueries.

on music related site users can different things artists, songs, albums etc. these "likes" stored in same table. want show list of artists ordered number of "likes" users friends , users. want show artists, have no likes @ all.

i have following query:

select `artists`.*,       // friend likes     (select count(*)      `likes`      like_type = 'artist'      , like_id = artists.id      , user_id in (1,2,3,4, etc) // ids of friends      group like_id     ) `friend_likes`,       // likes     (select count(*)      `likes`      like_type = 'artist'      , like_id = artists.id      group like_id     ) `all_likes`  artists order      friend_likes desc,      all_likes desc,      artists.name asc 

the query takes ± 1.5 seconds on artist table 2000 rows. i'm afraid takes longer , longer table gets bigger , bigger. tried using joins can't seem working because subqueries contain statements.

any ideas in right direction appreciated!

try using joins instead of subqueries:

select   artists.*, -- need this?   count(user_id) all_likes,   sum(user_id in (1, 2, 3, 4)) friend_likes artists left join likes l   on l.like_type = 'artist' , l.like_id = a.id group a.id order    friend_likes desc,    all_likes desc,    artists.name asc; 

if doesn't make query faster, try adding indices, or consider selecting less fields.