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. > > > > > > > > > > > > > > > >
