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..