List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 24 1999 2:07pm
Subject:Re: Reducing temporary table size
View as plain text  
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.

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

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Reducing temporary table sizePhilip Brown24 Sep
  • Re: Reducing temporary table sizeMartin Ramsch24 Sep
    • Re: Reducing temporary table sizeMichael Widenius25 Sep