CODEWITHSUNDEEP
phpmysqloptimizationjoin
Ben
Ben

Reputation: 249

trying to optimize mysql query but when i add ORDER BY its takes long time

this is my query

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

its realy fast, but in the EXPLAIN it don't show me it uses INDEXES (there is indexes). but when i add ORDER BY 'name' before the LIMIT its takes long time why? there is a way to solve it?

tables: users 150000, regular 50000, facebook 50000, twitter 50000, country 250 and growing!

Upvotes: 0

Views: 847

Answers (4)

Quassnoi
Quassnoi

Reputation: 425341

You need to create first 100 records from each name table separately, then union the results, join them with user and country, order and limit the output:

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

Create the following indexes:

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

Note that this query orders slightly differently from your original one: in this query, 'Ronnie James Dio' would be sorted after 'Ronnie Scott'.

Upvotes: 1

erKURITA
erKURITA

Reputation: 407

It takes a long time because it's a composite column, not a table column. The name column is a result of a case selection, and unlike simple selects with multiple join, MySQL has to use a different sorting algorithm for this kind of data.

I'm talking from ignorance here, but you could store the data in a temporary table and then sort it. It may go faster since you can create indexes for it but it won't be as fast, because of the different storage type.

UPDATE 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

Remember temporary tables are dropped upon connection termination, so you don't have to clean them, but you should anyway.

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

The use of functions on the columns prevent indexes from being used.

CONCAT(F.first_name,' ',F.last_name)

The result of the function is not indexed, even though the individual columns may be. Either you have to rewrite the conditions to query the name columns individually, or you have to store and index the result of that function (such as a "full name" column).

The index on [user.active] is unlikely to help you if most of the users are active.

I don't know what your application is all about, but I wonder if it hadn't been easier if you ditched the foreign keys in User table and instead put the UserID as a foreign key in the other tables instead.

Upvotes: 0

Geoffrey Wagner
Geoffrey Wagner

Reputation: 818

Without actually knowing your DB structure, and assuming you have all of the proper indexes on everything. An Order By statement takes some variable amount of time to sort the elements being returned by a query (index or not). If it is only 10 rows, it will seem almost instant, if you get 2000 rows, it will be a little slower, if you are sorting 15k rows joined across multiple tables, it is going to take some time to sort the returned result. Also make sure your adding indexes to the fields your sorting by. You may want to take the desired result and store everything in a presorted stub table for faster querying later as well (if you query this sorted result set often)

Upvotes: 1

Related Questions