List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 8 1999 3:27pm
Subject:Re: How do you specify all records in a WHERE statement?
View as plain text  
On Wed, 1999-09-08 15:41:19 +0100, Clinton Gormley wrote:
> Because we wanted to prepare a single statement and use placeholders
> to achieve the required effect, we did this :
> 
> ##For getting list of $JUMP bottles which match search criteria. 
> $c{sthWine} = $dbh->prepare(<<END);
> SELECT 	 B.bottleID
[...]
> WHERE 	 B.defaultVolume = 1
[...]
>      AND IF(?, W.categoryID = ?,1) 
>      AND IF(?, W.typeID = ?,1) 
>      AND IF(?, W.vintage = ?,1) 
>      AND IF(?, W.styleID = ?,1) 
>      AND IF(?, W.regionID = ?,1) 
>      AND IF(?, W.orgasmic = ?,1) 
>      AND IF(?, (W.name LIKE ?) OR (P.name LIKE ?),1) 
[...]
> So for each of the AND IF statements, we pass the length of the
> search criterion (thus 0 if we are not searching on this criterion).
> I'm assuming that if the IF statement evaluates to 1, then MySQL
> will optimise it out.

I just tested this, and I think MySQL (V3.22.19b at least) does not
optimize these IF terms, but it can optimize "0 AND" or "1 OR",
so you might change terms:

  IF(?, W.categoryID = ?,1) --> (? OR W.categoryID = ?)

Then you wouldn't have to pass the length, but vice-versa 1 to
skip the comparison, 0 otherwise.


> Which is likely to be faster, MySQL's optimisation or preparing new
> SQL statements in perl every time?

I'm quite sure, that preparing new SQL statements will be faster.
But you have to test it to be sure, there are too many unknown factors
to influence the actual speed ...

Regards,
  Martin

PS: Why do you guys always quote the complete article?  Isn't that
    a waste of space and bandwidth?  PLEASE, answer in private and
    not on the list, if you'd like to comment on this!
-- 
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
How do you specify all records in a WHERE statement?a rancid amoeba8 Sep
  • Re: How do you specify all records in a WHERE statement?Joshua Chamas8 Sep
    • Re: How do you specify all records in a WHERE statement?a rancid amoeba8 Sep
      • Re: How do you specify all records in a WHERE statement?Joshua Chamas8 Sep
        • Re: How do you specify all records in a WHERE statement?Michael Widenius8 Sep
      • Re: How do you specify all records in a WHERE statement?Jules Bean8 Sep
RE: How do you specify all records in a WHERE statement?Clinton Gormley8 Sep
  • Re: How do you specify all records in a WHERE statement?Martin Ramsch8 Sep
  • RE: How do you specify all records in a WHERE statement?Michael Widenius8 Sep