>>>>> "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
