List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 25 1999 6:38am
Subject:Re: Reducing temporary table size
View as plain text  
>>>>> "Martin" == Martin Ramsch <m.ramsch@stripped> writes:

Martin> On Fri, 1999-09-24 14:06:25 +0100, Philip Brown wrote:
>> I want to issue the following query (syntax simplified):
>> 
>> SELECT X.* FROM X LEFT JOIN Y GROUP BY X.ID HAVING COUNT(Y.NNN)>1
>> 
>> In other words, select records from X which have 2 or more linked
>> records in Y.

Martin> You don't need a LEFT JOIN here, because COUNT(Y.NNN) won't count NULL
Martin> columns anyway!
Martin> So
Martin>   SELECT X.* FROM X, Y WHERE ... GROUP BY X.ID HAVING COUNT(Y.NNN)>1
Martin> should also work and be much faster.

Hi!

Try doing:

SELECT SQL_SMALL_RESULT X.* FROM X LEFT JOIN Y GROUP BY X.ID HAVING COUNT(Y.NNN)>1

If there is only a small different X.ID values, the above should work
and it will only need a temporary row for each different X.ID.

You can also try MySQL 3.23 that can handle GROUP BY queries much
better...

Regards,
Monty
Thread
Reducing temporary table sizePhilip Brown24 Sep
  • Re: Reducing temporary table sizeMartin Ramsch24 Sep
    • Re: Reducing temporary table sizeMichael Widenius25 Sep