MySQL Forums
Forum List  »  Optimizer & Parser

Re: GROUP BY ,ORDER BY optimization help
Posted by: Rick James
Date: October 09, 2011 08:58PM

> Is still using temporary probably because of the different GROUP BY, ORDER BY.
If that is the question, don't worry about it. Temp tables are your friend for complex queries, not the villain.

You are correct about the key_len.
ENUM (up to 255 items) NOT NULL -- 1 byte
TINYINT NOT NULL -- 1 byte.

Further, since key_len=2 and
KEY `top_rated` (`active`,`unhacked`,`rating`,`order`),
then it stopped short of using rating and order, at least for the WHERE clause. It does not say whether it used the rest for the ORDER BY. However, it needs to do the GROUP BY before it gets to the ORDER BY.

g.id is UNIQUE within `games`. So why do the GROUP BY g.id? Because the JOINs are leading to more than one row per g.id. So, let's see if we can avoid that.

Here's one approach:
SELECT  g.id, g.name, g.playcount, g.rating,
        g.desc, g.nameid, 
        ( SELECT  GROUP_CONCAT(m.username SEPARATOR ', ')
            FROM  `game_hacker` as gh ON g.id = gh.gameid
            LEFT JOIN  `members` as m ON gh.userid = m.id ) AS authornames,
        ( SELECT  GROUP_CONCAT(m.id SEPARATOR ', ')
            FROM  `game_hacker` as gh ON g.id = gh.gameid
            LEFT JOIN  `members` as m ON gh.userid = m.id ) AS ids
    FROM  `games` as g
    WHERE  g.`active`='Yes'
      AND  g.unhacked=0
    ORDER BY  g.`rating` DESC,g.`order` DESC;
(I don't like it because it reaches into the other two table twice.)
Anyway, does it work 'correctly'? Is it any faster?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: GROUP BY ,ORDER BY optimization help
1335
October 09, 2011 08:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.