List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 8 1999 4:34pm
Subject:RE: How do you specify all records in a WHERE statement?
View as plain text  
>>>>> "Clinton" == Clinton Gormley <clint@stripped> writes:

Clinton> We need to do a similar thing, also in Perl. Because we wanted to
Clinton> prepare a single statement and use placeholders to achieve the required
Clinton> effect, we did this :

Clinton> ##For getting list of $JUMP bottles which match search criteria. 
Clinton> $c{sthWine} = $dbh->prepare(<<END);
Clinton> SELECT 	 B.bottleID
Clinton> 	,W.wineID	 
Clinton> 	,W.name
Clinton> 	,W.vintage 
Clinton> 	,B.orgasmicPrice 
Clinton> 	,B.volume 
Clinton> 	,R.ratingTotal 
Clinton> 	,W.orgasmic 
Clinton> 	,W.blurb
Clinton> FROM 	 WINE W
Clinton> 	,BOTTLE B
Clinton> 	,WINE_RATING R
Clinton> 	,PRODUCER P
Clinton> WHERE 	 B.defaultVolume = 1
Clinton> 	 AND B.extinct = 0
Clinton>      AND B.orgasmicPrice BETWEEN ? AND ?
Clinton>      AND IF(?, W.categoryID = ?,1) 
Clinton>      AND IF(?, W.typeID = ?,1) 
Clinton>      AND IF(?, W.vintage = ?,1) 
Clinton>      AND IF(?, W.styleID = ?,1) 
Clinton>      AND IF(?, W.regionID = ?,1) 
Clinton>      AND IF(?, W.orgasmic = ?,1) 
Clinton>      AND IF(?, (W.name LIKE ?) OR (P.name LIKE ?),1) 
Clinton>      AND B.wineID = W.wineID
Clinton>      AND W.wineID = R.wineID
Clinton>      AND W.producerID = P.producerID
Clinton> ORDER BY R.ratingTotal desc,B.orgasmicPrice
Clinton> LIMIT ?, $c{JUMP}
Clinton> END

Clinton> So for each of the AND IF statements, we pass the length of the search
Clinton> criterion (thus 0 if we are not searching on this criterion).  I'm
Clinton> assuming that if the IF statement evaluates to 1, then MySQL will
Clinton> optimise it out.

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

Clinton> Thanks

Clinton> Clint

Hi!

MySQL will optimize away constant expression, but currently MySQL
doesn't optimize the case of IF(constant,?,?).

In this case it's faster to prepare a new SQL statement for each
query.

(Another problem is that if you anywhere refer to a column that isn't 
an index, MySQL can't solve the query by only using the index tree.
This is because the check which indexes can be used is done very
early)

Regards,
Monty
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