MySQL Forums
Forum List  »  Optimizer & Parser

Re: GROUP BY ,ORDER BY optimization help
Posted by: Ciobanu MArius
Date: October 10, 2011 09:22AM

I like what explain says about your query but I don't quite understand what you try to do. first it gives an error "ON g.id = gh.gameid" g.id is not defined. if I remove that it groups all the names. and I can't understand what you try to do, if you could make it work I think would be the best option which I got now.

second I was thinking to another thing
something like this:
SELECT gd.*,
	    GROUP_CONCAT(m.username SEPARATOR ', ') AS authornames,
	    GROUP_CONCAT(m.id SEPARATOR ', ') AS ids FROM 
	(SELECT g.id, g.name, g.playcount, 
			g.rating, g.desc, g.nameid,g.order 
	 FROM `games` as g WHERE g.`active`='Yes' AND g.unhacked=0
	 ORDER BY `rating` DESC LIMIT 0,20) as gd
JOIN `game_hacker` as gh ON gd.id = gh.gameid
LEFT JOIN `members` as m ON gh.userid = m.id
GROUP BY gd.`id`

from what I know derived queries should be avoided but I don't know a way to test
best query, like you asked me, it works faster how do I test that ? I can't place it online having 3000 users browsing pages.
+----+-------------+------------+--------+---------------------------------------------------------------------------+-----------+---------+------------------+------+----------------+
| id | select_type | table      | type   | possible_keys                                                             | key       | key_len | ref              | rows | Extra          |
+----+-------------+------------+--------+---------------------------------------------------------------------------+-----------+---------+------------------+------+----------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                                                      | NULL      | NULL    | NULL             |   20 | Using filesort |
|  1 | PRIMARY     | gh         | ref    | gameid                                                                    | gameid    | 4       | gd.id            |    1 | Using index    |
|  1 | PRIMARY     | m          | eq_ref | PRIMARY                                                                   | PRIMARY   | 4       | arcade.gh.userid |    1 |                |
|  2 | DERIVED     | g          | ref    | active_2,active_3,cat,active_id,new_games,active_4,most_popular,top_rated | top_rated | 2       |                  | 2116 | Using where    |
+----+-------------+------------+--------+---------------------------------------------------------------------------+-----------+---------+------------------+------+----------------+
is that bad filesort on that 20 rows,
still don't get it why is not using `rating` from the `top_rated` key,
another thing which I can't understand is why is showing the result in ASC order
even if I add ORDER BY NULL after group by.
looking forward to hear from you.



Edited 1 time(s). Last edit at 10/10/2011 09:27AM by Ciobanu MArius.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: GROUP BY ,ORDER BY optimization help
1001
October 10, 2011 09:22AM


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.