at your mysql prompt set sort_area_size=100000000;
try this regards anandkl On 8/10/07, Mike Zupan <[EMAIL PROTECTED]> wrote: > > Which is the my.cnf entry I need to increase. I'm only getting around 4k > > back_log = 75 > skip-innodb > max_connections = 500 > key_buffer = 512M > sort_buffer_size = 256M > join_buffer_size = 128M > read_buffer_size = 128M > sort_buffer_size = 128M > table_cache = 1800 > thread_cache_size = 384 > wait_timeout = 7200 > connect_timeout = 10 > tmp_table_size = 32M > max_heap_table_size = 64M > max_allowed_packet = 64M > max_connect_errors = 1000 > read_rnd_buffer_size = 512M > bulk_insert_buffer_size = 8M > query_cache_limit = 38M > query_cache_size = 256M > query_cache_type = 1 > query_prealloc_size = 65536 > query_alloc_block_size = 131072 > default-storage-engine = MyISAM > > > On 8/9/07, Andrew Armstrong <[EMAIL PROTECTED]> wrote: > > > > It goes to a temporary table when MySQL does not have enough memory > > (allocated) to store the temporary results in memory, so it needs to > > create > > a temporary table on disk. > > > > Try increasing the memory buffer size or eliminating more rows from the > > query. > > > > -----Original Message----- > > From: Mike Zupan [mailto:[EMAIL PROTECTED] > > Sent: Friday, 10 August 2007 4:52 AM > > To: mysql@lists.mysql.com > > Subject: remove temporary table from SELECT query > > > > 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. > > > > >
