Adam Adam - 6 months ago 23
SQL Question

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;


As above the SELECT takes 5-6 seconds to run.

If I remove the Order By the SELECT 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?

thanks

Answer

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