Re: GROUP BY ,ORDER BY optimization help

[Ciobanu MArius]

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.