Re: remove temporary table from SELECT query
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 '-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.
Re: remove temporary table from SELECT query
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=1; On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: at your mysql prompt set sort_area_size=1; 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)
Re: remove temporary table from SELECT query
at your mysql prompt set sort_area_size=1; 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 '-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.
Re: remove temporary table from SELECT query
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=1; On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: at your mysql prompt set sort_area_size=1; 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 '-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
Re: remove temporary table from SELECT query
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=1; On 8/10/07, Ananda Kumar [EMAIL PROTECTED] wrote: at your mysql prompt set sort_area_size=1; 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 '-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
RE: remove temporary table from SELECT query
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 '-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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
