尝试优化mysql查询，但添加ORDER BY查询需要很长时间

Question

这是我的问题

SELECT U.id AS user_id,C.name AS country,
                CASE
                WHEN U.facebook_id > 0 THEN CONCAT(F.first_name,' ',F.last_name)
                WHEN U.twitter_id > 0 THEN T.name
                WHEN U.regular_id > 0 THEN CONCAT(R.first,' ',R.last)
                END AS name,
                FROM user U LEFT OUTER JOIN regular R
                ON U.regular_id = R.id
                LEFT OUTER JOIN twitter T
                ON U.twitter_id = T.id
                LEFT OUTER JOIN facebook F
                ON U.facebook_id = F.id
                LEFT OUTER JOIN country C
                ON U.country_id = C.id
                WHERE (CONCAT(F.first_name,' ',F.last_name) LIKE '%' OR T.name LIKE '%' OR CONCAT(R.first,' ',R.last) LIKE '%') AND U.active = 1
                LIMIT 100

它真的很快，但是在解释中它没有告诉我它使用了索引(有索引)。但是当我在限制前添加按'name‘排序时，为什么要花很长时间？有办法解决吗？

排行榜:用户150000，普通用户50000，脸书50000，推特50000，国家250，并且还在增长！

Answer 1

这需要很长时间，因为它是一个复合列，而不是一个表列。name列是案例选择的结果，与使用多个连接的简单selects不同，MySQL必须对这类数据使用不同的排序算法。

我在这里是出于无知，但您可以将数据存储在一个临时表中，然后对其进行排序。它可能会更快，因为你可以为它创建索引，但它不会那么快，因为不同的存储类型。

更新2011-01-26

CREATE TEMPORARY TABLE `short_select` 
       SELECT U.id AS user_id,C.name AS country,
            CASE
            WHEN U.facebook_id > 0 THEN CONCAT(F.first_name,' ',F.last_name)
            WHEN U.twitter_id > 0 THEN T.name
            WHEN U.regular_id > 0 THEN CONCAT(R.first,' ',R.last)
            END AS name,
            FROM user U LEFT OUTER JOIN regular R
            ON U.regular_id = R.id
            LEFT OUTER JOIN twitter T
            ON U.twitter_id = T.id
            LEFT OUTER JOIN facebook F
            ON U.facebook_id = F.id
            LEFT OUTER JOIN country C
            ON U.country_id = C.id
            WHERE (CONCAT(F.first_name,' ',F.last_name) LIKE '%' OR T.name LIKE '%' OR CONCAT(R.first,' ',R.last) LIKE '%') AND U.active = 1
            LIMIT 100;

ALTER TABLE `short_select` ADD INDEX(`name`); --add successive columns if you are going to order by them as well.

SELECT * FROM `short_select`
    ORDER BY 'name'; -- same as above

记住，临时表在连接终止时会被删除，所以你不必清理它们，但无论如何你都应该。

Answer 2

而实际上并不知道你的数据库结构，并且假设你拥有所有的正确的索引。一条Order By语句需要花费一些可变的时间来对查询返回的元素(索引或非索引)进行排序。如果只有10行，它看起来几乎是瞬间的，如果你有2000行，它将会稍微慢一点，如果你正在对多个表连接的15k行进行排序，那么对返回的结果进行排序将需要一些时间。此外，还要确保将索引添加到排序依据的字段中。您可能希望获得所需的结果，并将所有内容存储在预先排序的存根表中，以便以后更快地进行查询(如果您经常查询此排序的结果集)

Answer 3

您需要分别从每个name表创建第一个100记录，然后合并结果，使用user和country连接它们，对输出进行排序和限制：

SELECT  u.id AS user_id, c.name AS country, n.name
FROM    (
        SELECT  facebook_id AS id, CONCAT(F.first_name, ' ', F.last_name) AS name
        FROM    facebook
        ORDER BY
                first_name, last_name
        LIMIT 100
        UNION ALL
        SELECT  twitter_id, name
        FROM    twitter
        WHERE   twitter_id NOT IN
                (
                SELECT  facebook_id
                FROM    facebook
                )
        ORDER BY
                name
        LIMIT 100
        UNION ALL
        SELECT  regular_id, CONCAT(R.first, ' ', R.last)
        FROM    regular
        WHERE   regular_id NOT IN
                (
                SELECT  facebook_id
                FROM    facebook
                )
                AND 
                regular_id NOT IN
                (
                SELECT  twitter_id
                FROM    twitter
                )
        ORDER BY
                first, last
        LIMIT 100
        ) n
JOIN    user u
ON      u.id = n.id
JOIN    country с
ON      c.id = u.country_id

创建以下索引：

facebook (first_name, last_name)
twitter (name)
regular (first, last)

请注意，此查询与原始查询的顺序略有不同:在此查询中，'Ronnie James Dio‘将排在'Ronnie Scott’之后。