CODEWITHSUNDEEP
mysqlperformanceselect
Adam
Adam

Reputation: 20922

MySQL Select Slow because Order By

I have the following SELECT:

SELECT a.userid,
    (SELECT name FROM photos WHERE userid=a.userid AND type='profile' LIMIT 1) AS photo,
    (SELECT attractive FROM photos WHERE userid=a.userid AND type='profile' LIMIT 1) AS attractive,
    IF(a.domain="domanin.com",1,2) AS preferredDomain,
    IF(a.domain LIKE "%domain.com",1,2) AS preferredSubDomain
FROM users AS a
WHERE a.gender = 1
    AND a.visible = 1
    AND a.active = 1
    AND a.completed = 1
    AND a.approved = 1
HAVING photo IS NOT NULL
ORDER BY preferredDomain ASC,
    attractive DESC,
    a.lastlogin DESC,
    preferredSubDomain ASC
LIMIT 100;

This takes 5-6 seconds to run.

If I remove the Order By it takes 0.23 seconds to run.

I'm assuming this is because SELECT must generate the internal SELECTS before it can do the ordering? Is this right? The users table has 60,000 entries.

Can anyone give some advise on how to better structure this query?

Upvotes: 1

Views: 81

Answers (2)

Rick James
Rick James

Reputation: 142433

The subqueries are not the villains. With the ORDER BY all 60K rows must be evaluated before sorting. Without the ORDER BY possibly fewer rows need be looked at.

@StephanGarle's suggestion of a JOIN should speed up the query, especially since both subqueries are replaced by the one JOIN.

photos needs INDEX(type, userid) (in either order). But even better would be to have a "covering" index: (type, userid, name, attractive), where the first two are in either order and the last two are in either order.

There are two ways for photo to be NULL --

  • The row could exist, but name could be NULL, or
  • The row could not exist. (That is, there is no row in photos for that userid and type 'profile'.)

Which of those is it? To cover both cases, use LEFT JOIN.

This index (with the columns in any order) may help performance:

INDEX(gender, visible, active, completed, approved)

So...

SELECT  a.userid,
        p.name,
        p.attractive,
        IF(a.domain="domain.com", 1,2)       AS preferredDomain,
        IF(a.domain LIKE "%domain.com", 1,2) AS preferredSubDomain
    FROM  users AS a
    LEFT JOIN  photos AS p
              ON ( p.userid = a.userid
             AND   p.type = 'profile' )
    WHERE  a.gender = 1
      AND  a.visible = 1
      AND  a.active = 1
      AND  a.completed = 1
      AND  a.approved = 1
      AND  p.name IS NOT NULL
    ORDER BY  preferredDomain ASC, p.attractive DESC, a.lastlogin DESC,
              preferredSubDomain ASC
    LIMIT  100;

I am still unsure whether name and LEFT are correctly handled.

Upvotes: 0

Stephen Garle
Stephen Garle

Reputation: 299

Try using a join instead of nested queries, for example:

SELECT 
      a.userid, 
      p.name, 
      p.attractive, 
      IF(a.domain="domain.com",1,2) AS preferredDomain,
      IF(a.domain LIKE "%domain.com",1,2) AS preferredSubDomain 
FROM users AS a
JOIN photos AS p ON (p.userid = a.userid AND p.type = 'profile')
WHERE 
      a.gender = 1
      AND a.visible = 1
      AND a.active = 1
      AND a.completed = 1
      AND a.approved = 1
HAVING photo IS NOT NULL
ORDER BY preferredDomain ASC, attractive DESC, a.lastlogin DESC, preferredSubDomain ASC
LIMIT 100;

Documentation on join syntax can be found here: http://dev.mysql.com/doc/refman/5.7/en/join.html

Upvotes: 1

Related Questions