🚀 go-pugleaf

RetroBBS NetNews Server

Inspired by RockSolid Light RIP Retro Guy

Article View: borland.public.interbase.sql
Article #161

HAVING or WHERE

#161
From: Hs
Date: Tue, 24 Jun 2008 16:20
38 lines
1994 bytes

I have two Tables:

CREATE TABLE "BUY" 
(
  "CENTER_ID"	INTEGER NOT NULL,
  "ID"	INTEGER NOT NULL, 
  "FARMER_ID"	INTEGER NOT NULL,
  "FACTORY_ID"	INTEGER NOT NULL,
  "BUY_WEIGHT"	INTEGER,
 PRIMARY KEY ("CENTER_ID", "BUY_ID")
);

CREATE TABLE "FARMER" 
(
  "CENTER_ID"	INTEGER NOT NULL,
  "ID"	INTEGER NOT NULL,
  "NAME"	VARCHAR(25),
   PRIMARY KEY ("CENTER_ID", "ID")
);

I executed two queries:
1) SELECT FARMER.ID,SUM(BUY_WEIGHT) FROM FARMER INNER JOIN BUY ON FARMER.ID=BUY.FARMER_ID
  WHERE BUY.FACTORY_ID=1 AND BUY.CENTER_ID=1
  GROUP BY FARMER.ID

2)SELECT FARMER.ID,SUM(BUY_WEIGHT) FROM FARMER INNER JOIN BUY ON FARMER.ID=BUY.FARMER_ID
  WHERE BUY.FACTORY_ID=1 
  GROUP BY FARMER.ID
  HAVING BUY.CENTER_ID=1

for both the result was same but the execution of the first query longs more than 90(s) and of second query less than 1(s) !!
The plan for the first was: PLAN SORT (JOIN (FARMER NATURAL,BUY INDEX (RDB$PRIMARY4)))
and for second was: PLAN SORT (MERGE (SORT (BUY NATURAL),SORT (FARMER NATURAL)))
What is happening? Generaly what is the difference in resuls and in background between calling HAVING or WHERE for non aggregation fields?

Thanks.
Technical Details
Message-ID: <48611ee3$1@newsgroups.borland.com>
Path: arc1-ca.pugleaf.net!g2news1.google.com!news1.google.com!border1.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!newspeer1.nac.net!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail

Show Full Headers
Complete Article Headers
X-Google-Thread: 114a0e,8b9906a31c39f622,start
X-Google-Attributes: gid114a0e,domainid0,public,usenet
X-Google-Language: ENGLISH,ASCII-7-bit
From: "Hs" <prg_stu@yahoo.com>
Sender: "Hs" <prg_stu@yahoo.com>
Reply-To: "Hs" <prg_stu@yahoo.com>
Subject: HAVING or WHERE
Newsgroups: borland.public.interbase.sql
X-User-Info: 80.191.189.114     
NNTP-Posting-Host: 207.105.83.66
Message-ID: <48611ee3$1@newsgroups.borland.com>
Date: 24 Jun 2008 09:20:51 -0700
X-Trace: newsgroups.borland.com 1214324451 207.105.83.66 (24 Jun 2008 09:20:51 -0700)
Lines: 38
Path: arc1-ca.pugleaf.net!g2news1.google.com!news1.google.com!border1.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!newspeer1.nac.net!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail
X-Original-Bytes: 1821