Hi , "ORDER BY", will always use some temporary table for doing sort operation. For that matter, any group function, like min,max,group by,order by will use temporary table before displaying the final results.
Regards anandkl On 8/10/07, Mike Zupan <[EMAIL PROTECTED]> wrote: > > neither of those have fixed the issue.. I can create a sample database if > anyone wants it to be of some help. > > Mike > > On 8/10/07, Ananda Kumar <[EMAIL PROTECTED]> wrote: > > > > 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.cnf level. > > > > 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. > > > > > > > > > > > > > > > > > > > > > > >
