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