I have been pulling my hair out over a temporary table being created in the following query
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS entryid,title FROM friends_test INNER JOIN entries ON friendLink=userid AND userLink=2 order by entryid if I change userLink=2 to friendLink=2 it is fine and its very fast. If i leave it the query is around 2 seconds. +----+-------------+--------------+------+---------------------+----------+---------+-----------------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------------+----------+---------+-----------------------------------+------+---------------------------------+ | 1 | SIMPLE | friends_test | ref | userLink,friendLink | userLink | 3 | const | 458 | Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | userid | userid | 4 | photoblog.friends_test.friendLink | 11 | Using where | +----+-------------+--------------+------+---------------------+----------+---------+-----------------------------------+------+---------------------------------+ The above is an explain of the bad query Here is the table data for the friends_test and entries table CREATE TABLE `friends_test` ( `friendID` mediumint(8) NOT NULL auto_increment, `userLink` mediumint(8) unsigned NOT NULL, `friendLink` mediumint(8) unsigned NOT NULL, `status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`friendID`), KEY `userLink` (`userLink`), KEY `friendLink` (`friendLink`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74971 ; CREATE TABLE `entries` ( `entryid` mediumint(10) unsigned NOT NULL auto_increment, `userid` mediumint(8) unsigned default NULL, `title` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `photos` text, `sizes` mediumtext NOT NULL, `text` text character set utf8 collate utf8_unicode_ci, `category` int(6) unsigned default NULL, `created` int(10) unsigned default NULL, `ts` int(10) unsigned default '0', `modified` int(10) unsigned default NULL, `date` date NOT NULL default '0000-00-00', `comments` smallint(3) unsigned NOT NULL default '1', `views` mediumint(8) NOT NULL default '0', `dir` varchar(10) NOT NULL default 'photos', `server` varchar(20) NOT NULL default 'i1.photoblog.com', `notes` longtext character set utf8 collate utf8_unicode_ci NOT NULL, `titles` text character set utf8 collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`entryid`), KEY `userid` (`userid`), KEY `date` (`date`), KEY `created` (`created`), KEY `ts` (`ts`), FULLTEXT KEY `title` (`title`,`text`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=204306 ; any help or pointers is a BIG help.
