Re: GROUP BY ,ORDER BY optimization help

[Rick James]

> 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?