Also, its not good to set sort_buffer_size=256M, as this much of memory will get allocated to each session, and if u have more than 10 connections at any point of time then it will more than 2GB RAM will get allocated and system will be slow. I feel sort_buffer_size=1MB should be good in my.cnf
You need to increase this parameter only at session level and not at my.cnflevel. set sort_buffer_size=100000000; On 8/10/07, Ananda Kumar <[EMAIL PROTECTED]> wrote: > 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. > > > > > > > > > >
