Hi,
Thanks for helping me.
SELECT g.id, g.name, g.playcount, g.rating, g.desc, g.nameid,
GROUP_CONCAT(m.username SEPARATOR ', ') AS authornames,
GROUP_CONCAT(m.id SEPARATOR ', ') AS ids
FROM `games` as g
JOIN `game_hacker` as gh ON g.id = gh.gameid
LEFT JOIN `members` as m ON gh.userid = m.id
WHERE g.`active`='Yes' AND g.unhacked=0
GROUP BY g.`id`
ORDER BY g.`rating` DESC,g.`order` DESC
this would be the new query.which is using index; usign temporary
>First, does "GROUP BY order" give you the 'right' answer?
Yes. Table game_hacker, has gameid and userid in it, 1 gameid can have more
than 1 user, that's why I want to group the users to 1 game, it can be `order` which should be unique or id; it does not matter, order column is filled with game ids.
>I see no obvious reason for using LEFT; maybe use plain JOIN?
first LEFT JOIN is wrong, you are right should be plain JOIN, second, in the `game_hacker` there are games which are added by anonymous users and because of that there are 0 which does not exist in members table, but I need that games too.
>The first of these is redundant, and can be DROPped:
you are right. dropped.
>The 25 is nonsense. INT has a range of plus/minus 2 billion, regardless of the number after it.
I'm not sure you are right here. From what I know, the number after numberic types is the width of displaying number. it is to big, but is has nothing to do with INT range. correct if I'm wrong or I missunderstood
Any other ideas. The query isn't optimized. Is still using temporary probably because of the different GROUP BY, ORDER BY.
One question: in the explian key_len is 2; active 1 byte + unhacked 1 byte, means is using this 2 ones?
I appreciate all your efforts on this.
Thanks,
Marius.