MySQL Forums
Forum List  »  Optimizer & Parser

Re: GROUP BY ,ORDER BY optimization help
Posted by: Ciobanu MArius
Date: October 08, 2011 03:41AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: GROUP BY ,ORDER BY optimization help
1140
October 08, 2011 03:41AM


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.