CODEWITHSUNDEEP
mysqloptimizationquery-optimization
Vico
Vico

Reputation: 1256

Group BY and ORDER BY optimization

I'm trying to optimize a query on a table of 180 000 rows. 

    SELECT
            qid

            FROM feed_pool
            WHERE (
                (feed_pool.uid IN
                    (SELECT uid_followed
                        FROM followers
                        WHERE uid_follower = 123 AND unfollowed = 0) AND feed_pool.uid != 123)
                    OR feed_pool.tid IN (SELECT tid FROM follow_tags WHERE follow_tags.uid = 123)
                )
                GROUP BY feed_pool.qid ORDER BY feed_pool.id DESC LIMIT 20

The worst part of this query is not the WHERE clause, it is the GROUP BY and ORDER BY part.

Actually, if I do just the GROUP BY, it's fine. Just the ORDER BY is also fine. The problem is when I use both.

I have tried different indexes, and I'm now using an index on feedpool.qid and feedpool.uid.

A good hack is to first SELECT the last 20 rows (ORDER BY), and then do the GROUP BY. But obviously it's not exactly what I want to do, in some cases I don't have 20 rows in the end.

I really don't know what to do. I can change my structure if it optimizes my request (20 sec...). Really, every tip would be appreciated.

Thanks in advance.

Upvotes: 0

Views: 118

Answers (2)

echo_Me
echo_Me

Reputation: 37233

try this

 GROUP BY feed_pool.qid ORDER BY 1 DESC LIMIT 20

Upvotes: 1

AChudov
AChudov

Reputation: 214

Do you hear about JOIN? Subqueries is always bad for perfomance. Try something like this:

SELECT feed_pool.qid, followers.uid as fuid, follow_tags as ftuid
FROM feed_pool
    LEFT JOIN followers
        ON feed_pool.uid = followers.uid_followed 
            AND followers.uid_follower = 123 
            AND followers.unfollowed = 0 
            AND feed_pool.uid != 123
    LEFT JOIN follow_tags
        ON feed_pool.tid = follow_tags.tid 
        AND follow_tags.uid = 123
WHERE
    fuid IS NOT NULL
    OR ftuid IS NOT NULL
ORDER BY feed_pool.id DESC 
LIMIT 20

Upvotes: 1

Related Questions