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]
