RE: Very large temporary file(s)
I know I shouldn't repost, but it's been a week since the last response and I still don't have a resolution to my problem. In a nutshell, a have an INSERT query that is taking up over 800mB of file space even though the tables involved aren't anywhere near that size. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2008 4:52 PM To: 'Jerry Schwartz'; 'Ananda Kumar' Cc: 'mysql@lists.mysql.com' Subject: RE: Very large temporary file(s) Ananda, I tried your suggestion of adding ORDER BY NULL to the end of my query, but if anything it took up more disk space. I can see this becoming a critical problem for me. I just don't understand why this query, which is really not that crazy, is taking up 800mb of temporary space. -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2008 9:54 AM To: 'Ananda Kumar' Cc: mysql@lists.mysql.com Subject: RE: Very large temporary file(s) From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2008 10:50 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Very large temporary file(s) In mysql, GROUP BY also by default does sorting using ORDER BY, so you an avoid it by including ORDER BY NULL after GROUP BY, this should help. [JS] Huh. I would think that using an explicit ORDER BY with a GROUP BY would normally result in two sorts: one to do the grouping, and another to order the results after the grouping operation. I will give it a try. On 6/24/08, Jerry Schwartz [EMAIL PROTECTED] wrote: sigh In all of that information, I forgot to post the actual query: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(cust_topics.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id GROUP BY customers.customer_id; Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very large temporary file(s)
Ananda, I tried your suggestion of adding ORDER BY NULL to the end of my query, but if anything it took up more disk space. I can see this becoming a critical problem for me. I just don't understand why this query, which is really not that crazy, is taking up 800mb of temporary space. -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2008 9:54 AM To: 'Ananda Kumar' Cc: mysql@lists.mysql.com Subject: RE: Very large temporary file(s) From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2008 10:50 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Very large temporary file(s) In mysql, GROUP BY also by default does sorting using ORDER BY, so you an avoid it by including ORDER BY NULL after GROUP BY, this should help. [JS] Huh. I would think that using an explicit ORDER BY with a GROUP BY would normally result in two sorts: one to do the grouping, and another to order the results after the grouping operation. I will give it a try. On 6/24/08, Jerry Schwartz [EMAIL PROTECTED] wrote: sigh In all of that information, I forgot to post the actual query: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(cust_topics.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id GROUP BY customers.customer_id; Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very large temporary file(s)
From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2008 10:50 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Very large temporary file(s) In mysql, GROUP BY also by default does sorting using ORDER BY, so you an avoid it by including ORDER BY NULL after GROUP BY, this should help. [JS] Huh. I would think that using an explicit ORDER BY with a GROUP BY would normally result in two sorts: one to do the grouping, and another to order the results after the grouping operation. I will give it a try. On 6/24/08, Jerry Schwartz [EMAIL PROTECTED] wrote: sigh In all of that information, I forgot to post the actual query: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(cust_topics.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id GROUP BY customers.customer_id; Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large temporary file(s)
True, if you explicitly use group by and order by, there would a group operation after which sorting happens, but the same would happen if you use just GROUP BY. That how mysql works. On 6/25/08, Jerry Schwartz [EMAIL PROTECTED] wrote: *From:* Ananda Kumar [mailto:[EMAIL PROTECTED] *Sent:* Tuesday, June 24, 2008 10:50 PM *To:* Jerry Schwartz *Cc:* mysql@lists.mysql.com *Subject:* Re: Very large temporary file(s) In mysql, GROUP BY also by default does sorting using ORDER BY, so you an avoid it by including ORDER BY NULL after GROUP BY, this should help. *[JS] Huh. I would think that using an explicit ORDER BY with a GROUP BY would normally result in two sorts: one to do the grouping, and another to order the results after the grouping operation.* * * *I will give it a try.* On 6/24/08, *Jerry Schwartz* [EMAIL PROTECTED] wrote: sigh In all of that information, I forgot to post the actual query: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(cust_topics.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id GROUP BY customers.customer_id; Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very large temporary file(s)
From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2008 10:55 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Very large temporary file(s) True, if you explicitly use group by and order by, there would a group operation after which sorting happens, but the same would happen if you use just GROUP BY. That how mysql works. [JS] So how would adding an ORDER BY save space in the temporary file(s)? On 6/25/08, Jerry Schwartz [EMAIL PROTECTED] wrote: From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2008 10:50 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Very large temporary file(s) In mysql, GROUP BY also by default does sorting using ORDER BY, so you an avoid it by including ORDER BY NULL after GROUP BY, this should help. [JS] Huh. I would think that using an explicit ORDER BY with a GROUP BY would normally result in two sorts: one to do the grouping, and another to order the results after the grouping operation. I will give it a try. On 6/24/08, Jerry Schwartz [EMAIL PROTECTED] wrote: sigh In all of that information, I forgot to post the actual query: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(cust_topics.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id GROUP BY customers.customer_id; Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com http://www.the-infoshop.com/ www.giiexpress.com http://www.giiexpress.com/ www.etudes-marche.com http://www.etudes-marche.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large temporary file(s)
In mysql, GROUP BY also by default does sorting using ORDER BY, so you an avoid it by including ORDER BY NULL after GROUP BY, this should help. On 6/24/08, Jerry Schwartz [EMAIL PROTECTED] wrote: sigh In all of that information, I forgot to post the actual query: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(cust_topics.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id GROUP BY customers.customer_id; Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large temporary file(s)
sigh In all of that information, I forgot to post the actual query: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(cust_topics.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id GROUP BY customers.customer_id; Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very large temporary file(s)
I'm running MySQL 4.1.22-standard Community on CentOS. My problem is a query
that is using about 800mb for what I assume is a temporary sort file, and
I'm hoping that I can do something about it.
The purpose of this query is to populate an unnormalized table with data
from several other tables. I do this to make it easier for MS Access users
to use simple filters to see subsets of the data without having to construct
queries that have to be done as pass-through SQL.
This post is going to be long, so that you can see exactly what is going on.
I don't want to leave anything out that might be critical. Sorry...
Here are the tables involved:
Table: customers
Create Table: CREATE TABLE `customers` (
`customer_id` int(11) NOT NULL auto_increment,
`priority` int(3) NOT NULL default '0',
`account_id` int(2) NOT NULL default '0',
`sal` varchar(50) NOT NULL default '',
`first_name` varchar(125) NOT NULL default '',
`last_name` varchar(125) NOT NULL default '',
`company` varchar(255) NOT NULL default '',
`dept` varchar(255) NOT NULL default '',
`position` varchar(255) NOT NULL default '',
`address_1` varchar(255) NOT NULL default '',
`address_2` varchar(255) NOT NULL default '',
`address_3` varchar(255) NOT NULL default '',
`zip` varchar(15) NOT NULL default '',
`country` varchar(50) NOT NULL default '',
`phone` varchar(255) NOT NULL default '',
`fax` varchar(50) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
`entry_date` datetime default NULL,
`follow_up` date default NULL,
`action` varchar(50) NOT NULL default '',
`stage_id` int(11) default '0',
`status` varchar(50) NOT NULL default '',
`exp_price` decimal(8,2) default '0.00',
`input_source` varchar(255) NOT NULL default '',
`input_date` date default NULL,
`interest_category` varchar(255) NOT NULL default '',
`interest_subcategory` varchar(255) NOT NULL default '',
`interest_keyword` varchar(255) NOT NULL default '',
`db_entry` tinyint(3) NOT NULL default,
`hidden` tinyint(1) NOT NULL default '0',
`email_status` set('Y','N') NOT NULL default 'Y',
`dm_status` set('Y','N') NOT NULL default 'Y',
`num_products_purchased` smallint(6) default NULL,
`key_account` set('Y','N') NOT NULL default 'N',
`phone2` varchar(255) NOT NULL default '',
PRIMARY KEY (`customer_id`),
KEY `account_id` (`account_id`),
KEY `priority_id` (`priority`),
KEY `stage_id` (`stage_id`),
KEY `email` (`email`),
KEY `last_name_index` (`last_name`)
) ENGINE=MyISAM AUTO_INCREMENT=73717 DEFAULT CHARSET=utf8
***
Table: stage
Create Table: CREATE TABLE `stage` (
`stage_id` int(11) NOT NULL auto_increment,
`stage_name` varchar(15) default NULL,
PRIMARY KEY (`stage_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
**
Table: cust_topics
Create Table: CREATE TABLE `cust_topics` (
`cust_topic_id` int(11) NOT NULL auto_increment,
`topic_code` varchar(15) NOT NULL default '',
`customer_id` int(11) NOT NULL default '0',
PRIMARY KEY (`cust_topic_id`),
KEY `topic_id` (`customer_id`),
KEY `topic_code` (`topic_code`)
) ENGINE=MyISAM AUTO_INCREMENT=143201 DEFAULT CHARSET=utf8
**
Table: account
Create Table: CREATE TABLE `account` (
`account_id` int(11) NOT NULL auto_increment,
`account_name` char(2) character set latin1 default NULL,
`real_name` varchar(30) NOT NULL default '' COMMENT 'Real name associated
with account',
PRIMARY KEY (`account_id`),
UNIQUE KEY `account_name` (`account_name`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8
**
Table: consolidated_customer_data
Create Table: CREATE TABLE `consolidated_customer_data` (
`customer_id` int(11) NOT NULL default '0',
`acct_name` varchar(6) default NULL,
`email` varchar(60) default NULL,
`email_status` set('Y','N') NOT NULL default 'Y',
`dm_status` set('Y','N') NOT NULL default '',
`status` varchar(50) NOT NULL default '',
`last_name` varchar(125) default NULL,
`first_name` varchar(125) default NULL,
`sal` varchar(50) default NULL,
`company` varchar(255) default NULL,
`address_1` varchar(255) default NULL,
`address_2` varchar(255) default NULL,
`address_3` varchar(255) default NULL,
`country` varchar(50) default NULL,
`zip` varchar(15) default NULL,
`input_source` varchar(255) default NULL,
`interest_category` varchar(255) NOT NULL default '',
`interest_subcategory` varchar(255) NOT NULL default '',
`topic_list` text,
`stage` varchar(255) NOT NULL default '',
PRIMARY KEY (`customer_id`),
KEY `acct_name` (`acct_name`),
KEY `email` (`email`),
KEY `last_name` (`last_name`),
KEY `company` (`company`),
KEY `country` (`country`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
***
Before putting the data into the table
RE: Very large temporary file(s)
Nuts: not only did I write a huge message, but I made a booboo up at the top! I won't repost the whole thing. I'm running MySQL 4.1.22-standard Community on CentOS. My problem is a query that is using about 800mb for what I assume is a temporary sort file, [JS] That should be 800Gb. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very large temporary file(s)
Double nuts! I corrected the wrong number. This time, I am reading my message more carefully. The temporary space used is indeed 800 MEGABYTES! I still need help, though. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
