Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
From: Jerry Schwartz (jschwartzthe-infoshop.com)
Date: Mon Aug 25 2008 - 16:54:01 CDT
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
INSERT INTO consolidated_customer_data
CONCAT("|", GROUP_CONCAT(giiexpr_db.topic.topic_code SEPARATOR "|"),
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 =
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.
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql