From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2008 10:55 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Very large temporary file(s)
True, if you explicitly use group by and order by, there would a group operation after which sorting happens, but the same would happen if you use just "GROUP BY". That how mysql works. [JS] So how would adding an ORDER BY save space in the temporary file(s)? On 6/25/08, Jerry Schwartz <[EMAIL PROTECTED]> wrote: 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 <http://www.the-infoshop.com/> www.giiexpress.com <http://www.giiexpress.com/> www.etudes-marche.com <http://www.etudes-marche.com/> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
