***BOGO*** RE: Very large temporary file(s)

["Jerry Schwartz" <jschwartz@xxxxxxxxxxxxxxxx>]

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:jschwartz@xxxxxxxxxxxxxxxx]
>Sent: Wednesday, June 25, 2008 9:54 AM
>To: 'Ananda Kumar'
>Cc: mysql@xxxxxxxxxxxxxxx
>Subject: RE: Very large temporary file(s)
>
>From: Ananda Kumar [mailto:anandkl@xxxxxxxxx]
>Sent: Tuesday, June 24, 2008 10:50 PM
>To: Jerry Schwartz
>Cc: mysql@xxxxxxxxxxxxxxx
>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 <jschwartz@xxxxxxxxxxxxxxxx> 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/mysql?unsub=anandkl@xxxxxxxxx
>
>





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx