From: Ananda Kumar [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 26, 2008 12:09 AM
To: Jerry Schwartz
Cc: mysql
Subject: Re: Huge temporary file
what is the value for tmpdir parameter in you my.cnf.
[JS] I don't have one. The /tmp file system is what is being consumed. I
should add that the temporary space being used is many times the size of any
of the tables involved.
My entire /etc/my.cnf file looks like this:
[mysqld]
set-variable = max_connections=500
set-variable = ft_min_word_len=3
set-variable = character_set_server=utf8
safe-show-database
regards
anandkl
On 8/26/08, Jerry Schwartz <[EMAIL PROTECTED]> wrote:
Awhile back I was having trouble with an INSERT ... SELECT taking up an
enormous amount of temporary file space. I've narrowed down, in fact
eliminated, my problem by making a minor change. Here is my original INSERT
command:
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(giiexpr_db.topic.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
LEFT JOIN giiexpr_db.topic ON cust_topics.topic_id =
giiexpr_db.topic.topic_id
GROUP BY customers.customer_id;
When I removed the field `stage_name` from both the query and the
`consolidated_customer_data` table, the operation stopped using temporary
files altogether! I'm at a loss as to why, other than that I must have hit
some threshold. If anyone can tell me what I need to change in my
configuration, I'd appreciate it.
The `stage` table is very small, it has only 9 rows.
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
I can supply the structures of the other tables, but I wanted to keep this
post reasonably short.
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]
