speed up mysql query with multiple subqueries

Question

I'm wondering if there's a way to speed up a mysql query which is ordered by multiple subqueries.

On a music related site users can like different things like artists, songs, albums etc. These "likes" are all stored in the same table. Now I want to show a list of artists ordered by the number of "likes" by the users friends and all users. I want to show all artists, also those who have no likes at all.

I have the following query:

SELECT `artists`.*, 

    // friend likes
    (SELECT COUNT(*)
     FROM `likes`
     WHERE like_type = 'artist'
     AND like_id = artists.id
     AND user_id IN (1,2,3,4, etc) // ids of friends
     GROUP BY like_id
    ) AS `friend_likes`, 

    // all likes
    (SELECT COUNT(*)
     FROM `likes`
     WHERE like_type = 'artist'
     AND like_id = artists.id
     GROUP BY like_id
    ) AS `all_likes`

FROM artists
ORDER BY 
    friend_likes DESC, 
    all_likes DESC, 
    artists.name ASC

The query takes ± 1.5 seconds on an artist table with 2000 rows. I'm afraid that this takes longer and longer as the table gets bigger and bigger. I tried using JOINS by can't seem to get this working because the subqueries contain WHERE statements.

Any ideas in the right direction would be greatly appreciated!

In before others - what do you get when you put EXPLAIN before your SELECT statement? — Michael J.V.
– Michael J.V., Commented Apr 12, 2011 at 11:42

Martijn · Accepted Answer · 2011-04-12 11:48:03Z

3

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.

answered Apr 12, 2011 at 11:48

Martijn

5,6915 gold badges41 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

d-live Over a year ago

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.

Martijn Over a year ago

@d-live: I can imagine the optimizer has replaced each subquery by a join, but will it also put the two subqueries together?

d-live Over a year ago

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 :)

smek Over a year ago

@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

Martijn Over a year ago

@smek: just another brain wave; have you made like_type an enum?

|

n8wrl · Accepted Answer · 2011-04-12 11:44:48Z

0

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.

answered Apr 12, 2011 at 11:44

n8wrl

19.9k4 gold badges70 silver badges106 bronze badges

Comments

Johan · Accepted Answer · 2011-04-12 13:59:44Z

0

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

edited Apr 12, 2011 at 13:59

Johan

77.4k28 gold badges204 silver badges346 bronze badges

answered Apr 12, 2011 at 12:40

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

7 Comments

Johan Over a year ago

IN(1,2,3,4) can be rewritten as BETWEEN 1 AND 4, reducing 4 tests into 2.

DRapp Over a year ago

@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.

Johan Over a year ago

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.

DRapp Over a year ago

@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.

smek Over a year ago

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.

|

Collectives™ on Stack Overflow

speed up mysql query with multiple subqueries

3 Answers 3

6 Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related