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]