what is the value for tmpdir parameter in you my.cnf.
regards
anandkl
On 8/26/08, Jerry Schwartz <jschwartz@stripped> 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/mysql?unsub=1
>
>
