MySQL Forums
Forum List  »  Optimizer & Parser

GROUP BY ,ORDER BY optimization help
Posted by: Ciobanu MArius
Date: October 06, 2011 09:31AM

Hi,
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
     LEFT 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.`order`
     ORDER BY g.`rating` DESC, g.`order` DESC
explain looks like:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	g 	ref 	active,active_2,active_3,cat,active_id,new_games,t... 	top_rated 	2 	const,const 	2116 	Using where; Using temporary; Using filesort
1 	SIMPLE 	gh 	ref 	gameid 	gameid 	4 	arcade.g.id 	1 	Using index
1 	SIMPLE 	m 	eq_ref 	PRIMARY 	PRIMARY 	4 	arcade.gh.userid 	1

games table create
CREATE TABLE `games` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `nameid` varchar(255) COLLATE utf8_bin NOT NULL,
 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `desc` text COLLATE utf8_bin NOT NULL,
 `time` int(10) NOT NULL DEFAULT '0',
 `width` int(10) NOT NULL DEFAULT '0',
 `height` int(10) NOT NULL DEFAULT '0',
 `cat` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `rating` int(25) NOT NULL DEFAULT '0',
 `ratingcount` int(25) NOT NULL DEFAULT '0',
 `totalrating` int(25) NOT NULL DEFAULT '0',
 `type` enum('IMAGE','MOV','MPG','AVI','FLV','WMV','SWF','extlink','DCR','CustomCode') CHARACTER SET latin1 NOT NULL DEFAULT 'SWF',
 `authorsite` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `authorname` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `sponsorsite` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `sponsorname` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `gameurl` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `code` text CHARACTER SET latin1 NOT NULL,
 `playcount` int(10) NOT NULL DEFAULT '0',
 `weeklyplays` int(10) NOT NULL DEFAULT '0',
 `flags` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `instructions` text COLLATE utf8_bin NOT NULL,
 `keywords` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `disphtml` enum('Config','No','Yes') CHARACTER SET latin1 NOT NULL DEFAULT 'Config',
 `disphtmlcode` text CHARACTER SET latin1 NOT NULL,
 `order` int(10) NOT NULL DEFAULT '0',
 `active` enum('Yes','No') CHARACTER SET latin1 NOT NULL DEFAULT 'Yes',
 `broken` enum('Yes','No') CHARACTER SET latin1 NOT NULL DEFAULT 'No',
 `use_secured` int(10) NOT NULL,
 `requested_by` varchar(250) CHARACTER SET latin1 NOT NULL,
 `notes` text CHARACTER SET latin1 NOT NULL,
 `unhacked` tinyint(1) NOT NULL DEFAULT '0',
 `hacked_id` int(11) NOT NULL DEFAULT '0',
 `game_size` int(11) NOT NULL DEFAULT '0',
 `loader` tinyint(4) NOT NULL DEFAULT '0',
 `featured` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `active` (`active`,`unhacked`),
 KEY `active_2` (`active`,`unhacked`,`order`),
 KEY `nameid` (`nameid`,`unhacked`),
 KEY `active_3` (`active`,`unhacked`,`playcount`),
 KEY `cat` (`active`,`cat`,`unhacked`,`order`),
 KEY `hacked_id` (`hacked_id`),
 KEY `active_id` (`active`,`id`),
 KEY `new_games` (`active`,`unhacked`,`featured`,`order`),
 KEY `top_rated` (`active`,`unhacked`,`rating`,`order`),
 KEY `active_4` (`active`,`unhacked`,`order`,`rating`)
) ENGINE=MyISAM AUTO_INCREMENT=3977 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

sorry about columns naming not my work.

the problem is that like you see: where,group by,order by which is pretty bad,
and I don't know how to optimize it. Any help is appreciated.

Marius

Options: ReplyQuote


Subject
Views
Written By
Posted
GROUP BY ,ORDER BY optimization help
3469
October 06, 2011 09:31AM


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.