MySQL Select Slow 因为 Order By

Question

我有以下选择:

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;

这需要 5-6 秒才能运行。

如果我删除 Order By，运行需要 0.23 秒。

我假设这是因为 SELECT 必须生成内部 SELECTS 才能进行排序？这是正确的吗？用户表有 60,000 个条目。

谁能就如何更好地构造此查询提供一些建议？

Answer 1

尝试使用连接而不是嵌套查询，例如:

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;

有关连接语法的文档可在此处找到: http://dev.mysql.com/doc/refman/5.7/en/join.html