SEO

I'm currently using this query:

SELECT `characters`.`member_id`,
`characters`.`name`,
`characters`.`class`,
`characters`.`level`,
`characters`.`server`,
`characters`.`zone`,
UNIX_TIMESTAMP( `characters`.`last_online`) AS
'last_online_stamp',
DATE_FORMAT( DATE_ADD(`characters`.`last_online`, INTERVAL 0 HOUR
), '%a %b %D, %l:%i %p' ) AS 'last_online',

`members`.`note`,
IF( `members`.`note` IS NULL OR `members`.`note` = '', 1, 0 ) AS
'nisnull',
`members`.`guild_rank`,
`members`.`guild_title`,
`members`.`status`,
`players`.`race`,
`players`.`RankName`,
`players`.`RankInfo`,
IF( `players`.`RankInfo` IS NULL OR `players`.`RankInfo` = '0', 1,
0 ) AS 'risnull',
`players`.`exp`,
`players`.`clientLocale`,
`players`.`RankIcon`,
`players`.`Rankexp`,
`players`.`hearth`,
IF( `players`.`hearth` IS NULL OR `players`.`hearth` = '', 1, 0 )
AS 'hisnull',
`players`.`dateupdatedutc` AS 'last_update',
DATE_FORMAT( DATE_ADD(`players`.`dateupdatedutc`, INTERVAL 0 HOUR
), '%a %b %D, %l:%i %p' ) AS 'last_update_format',
IF( `players`.`dateupdatedutc` IS NULL OR `players`.
`dateupdatedutc` = '', 1, 0 ) AS 'luisnull',

`proftable`.`professions`

FROM `roster_characters` AS characters
LEFT JOIN `roster_members` AS members
ON `characters`.`member_id` = `members`.`member_id`
LEFT JOIN `roster_players` AS players
ON `members`.`member_id` = `players`.`member_id`
LEFT JOIN (SELECT `member_id`,
GROUP_CONCAT( CONCAT( `skill_name` , '|', `skill_level` ) ) AS
'professions'
FROM `roster_skills`
GROUP BY `member_id`) AS proftable
ON `members`.`member_id` = `proftable`.`member_id`
WHERE `members`.`guild_id` = 2
ORDER BY `characters`.`level` DESC, `characters`.`name` ASC;

I inserted the subquery to get rid of a seperate query for each line,
which drastically improved my performance. But it still seems a bit ugly
to me, because of the GROUP_CONCAT. Any ideas? The EXPLAIN for this
statement gives:

table type possible_keys key key_len ref
rows Extra
1 PRIMARY characters ALL PRIMARY NULL NULL NULL
317 Using temporary; Using filesort
1 PRIMARY members eq_ref PRIMARY PRIMARY
4 roster180.characters.member_id 1 Using where
1 PRIMARY players eq_ref PRIMARY PRIMARY
4 roster180.members.member_id 1
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5

2 DERIVED roster_skills ALL NULL NULL NULL NULL
84 Using filesort

--
PleegWat
Remove caps to reply