Speed up mysql query with multiple subqueries?

Try using JOIN s instead of subqueries: SELECT artists. *, -- do you really need all this? Count(user_id) AS all_likes, sum(user_id IN (1, 2, 3, 4)) AS friend_likes FROM artists a LEFT JOIN likes l ON l.

Like_type = 'artist' AND l. Like_id = a. Id GROUP BY a.Id ORDER BY friend_likes DESC, all_likes DESC, artists.

Name ASC If this doesn't make the query faster, try adding indices, or consider selecting less fields.

Try using JOINs instead of subqueries: SELECT artists. *, -- do you really need all this? Count(user_id) AS all_likes, sum(user_id IN (1, 2, 3, 4)) AS friend_likes FROM artists a LEFT JOIN likes l ON l.

Like_type = 'artist' AND l. Like_id = a. Id GROUP BY a.Id ORDER BY friend_likes DESC, all_likes DESC, artists.

Name ASC; If this doesn't make the query faster, try adding indices, or consider selecting less fields.

I am afraid this is going to help in performance. The internal query optimizer has probably already reduced the query as much as it can under the circumstances of his environment. – d-live Apr 12 at 11:56 @d-live: I can imagine the optimizer has replaced each subquery by a join, but will it also put the two subqueries together?

– Martijn Apr 12 at 12:03 Aha yes. I commented on the JOIN syntax vs the Non JOIN syntax. Yes you are right in combining the subqueries togeather +1 for that :) – d-live Apr 12 at 12:26 @d-live: I added an index on the field likes.

Like_id and the query runs much faster now, ± 0.03 seconds. Is it oke to add an index to a field when it can refer to different columns in different tables? (album_id, song_id or artist_id).

@Martijn: Thanks for showing me how to do this with a JOIN! Query looks much neater now but strangly runs slower. ± 1.7 seconds without the indexed field and ± 0.06 with the indexed field, against ± 1.5 and ± 0.03 – smek Apr 12 at 12:43 @smek: just another brain wave; have you made like_type an enum?

– Martijn Apr 12 at 13:52.

Try rolling up into a query using inline IF() and go through the table/join ONCE SELECT STRAIGHT_JOIN artists. * , LikeCounts. AllCount , LikeCounts.

FriendLikeCount FROM (SELECT like_id , count(*) AllCount , sum( If( User_id in ( 1, 2, 3, 4 ), 1, 0 ) as FriendLikeCount FROM friend_likes WHERE like_type = 'artist' GROUP BY like_id ) LikeCounts JOIN artists ON LikeCounts. Like_id = artists. Id ORDER BY LikeCounts.

FriendLikeCount DESC , LikeCounts. AllCount DESC , artists. Name ASC.

IN(1,2,3,4) can be rewritten as BETWEEN 1 AND 4, reducing 4 tests into 2. – Johan Apr 12 at 13:50 @Johan, yes, but the person posting the question was actually offering a list that could be 1, 8, 12, 38, 53, 2300... I just posted as simple sample. – DRapp Apr 12 at 14:09 The original question stated 1,2,3, etc.So the between made sense.

If the list is truly random, than an inner join on user_id might be more in order. – Johan Apr 12 at 14:24 @Johan, I agree with a possible inner join... However, editing content of posts by others shouldn't be messed with unless substantive purpose. Just because you have a different SQL styling of statements should not be done "just because".

The way it was originally entered showed easier the relationship of the first query joined to the next... Leave other people's styling alone unless its completely unformatted by a user who doesn't get the { } for code formatting. – DRapp Apr 12 at 14:39 The list with user_ids is random. I just stated 1,2,3 as an example.

@Johan, your query only returns the artists who have one or more likes, not all artists. – smek Apr 12 at 15:00.

You need to break this down a bit to see where the time goes. You're absolutely right that 1.5 sec on 2000 rows won't scale well. I suspect you need to look at indexes and foreign-key relationships.

Look at each count/group-by query individually to get them tuned as best you can then recombine.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.