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.

Reply via email to