Large Query (Using temporary; Using filesort;) etc..

[Jarin]

I have a rather large query that I've constructed to save me some time and resources in a PHP script, however when using EXPLAIN on the query I get 'using where; using temporary; using filesort' which I know is never a good thing on heavy-load servers. So, if anyone could give me some advice as to how to optimize this query I would greatly appreciate it.

Also, for the record, I have indexed the 'groupid' and 'type' columns already.

Here's the table:
[code]
CREATE TABLE `itemgen` (
  `id` int(10) NOT NULL auto_increment,
  `type` char(10) default NULL,
  `groupid` char(10) default NULL,
  `priority` tinyint(1) unsigned NOT NULL default '1',
  `slot` char(20) default NULL,
  `skill` char(15) default NULL,
  `weight` int(10) default NULL,
  `title` char(25) NOT NULL,
  `stats` varchar(100) NOT NULL,
  `requirements` varchar(100) NOT NULL,
  `other` varchar(100) NOT NULL,
  `icon` varchar(30) default NULL,
  `tags` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `group` (`groupid`(3)),
  KEY `type` (`type`(3)),
  KEY `code` (`id`)
[/code]

And the query:
[code]
SELECT
CONCAT(
IF(ig1.title != '', CONCAT(ig1.title, ' '), ''),
IF(ig3.title != '', CONCAT(ig2.title, ' '), ig2.title),
ig3.title
) AS name,
CONCAT(
IF(ig2.stats != '', CONCAT(ig1.stats, '_'), ig1.stats),
IF(ig3.stats != '', CONCAT(ig2.stats, '_'), ig2.stats),
ig3.stats
) AS stats,
CONCAT(
IF(ig2.requirements != '', CONCAT(ig1.requirements, '_'), ig1.requirements),
IF(ig3.requirements != '', CONCAT(ig2.requirements, '_'), ig2.requirements),
ig3.requirements
) AS requirements,
CONCAT(
IF(ig2.other != '', CONCAT(ig1.other, '_'), ig1.other),
IF(ig3.other != '', CONCAT(ig2.other, '_'), ig2.other),
ig3.other
) AS other,
CONCAT(
IF(ig2.tags != '', CONCAT(ig1.tags, '_'), ig1.tags),
IF(ig3.tags != '', CONCAT(ig2.tags, '_'), ig2.tags),
ig3.tags
) AS tags,
CONCAT(
ig1.id, '_',
ig2.id, '_', 
ig3.id
) AS code,
ig2.skill AS skill,
(
ig1.weight+
ig2.weight+
ig3.weight
) AS weight,
ig2.icon AS icon,
ig2.slot AS slot
FROM itemgen AS ig1
JOIN itemgen AS ig2
JOIN itemgen AS ig3
WHERE
ig1.type='prefix' AND ig2.type='affix' AND ig3.type='suffix'
AND
ig1.groupid='test' AND ig2.groupid='test' AND ig3.groupid='test'
ORDER BY RAND() LIMIT 1
[/code]

EXPLAIN returns this:
[code]1 SIMPLE ig1 ref group,type type 4 const 1 Using where; Using temporary; Using filesort
1 SIMPLE ig3 ref group,type type 4 const 1 Using where
1 SIMPLE ig2 ref group,type type 4 const 2 Using where
[/code]

The first row also includes 'Using filesort' but it looks like the forum truncated it..

[fenway]

That's a lot of work for the DB to do... you can't do all that string manipulation application-side?  Anyway, it's the random row that's killing you.  There are a number of ways to do this efficiently.