|Perl Monk, Perl Meditation|
DBD::mysql and binding placeholdersby dragonchild (Archbishop)
|on Sep 22, 2004 at 15:51 UTC||Need Help??|
I was just about to post a SoPW about this when I realized my "mistake". (Thank you, oh mighty Monasterial teddy bear!)
MySQL is very datatype dependent. Let's say you have the following table
with a million rows, indexed properly, and you do the following in Perl.
You are going to be very unpleasantly surprised by how long it takes. Then, if you're like me, you'll debug it by going into the mysql commandline and executing the following statement:
That query is going to execute very quickly. Up to 30 times quicker. And, you're going to be left scratching your head. I mean, the two queries are equivalent, right?
Actually, they're not. Using DBI->trace(2), you'll notice something slightly different about them. What DBD::mysql is actually passing to the database is
Those quotes make all the difference in the world. MySQL is great, but doesn't do heterogenous comparisons very well. This is one of the few places where I'll agree that other RDBMSes, like Oracle, beat MySQL out. And, it's such an easy mistake to make, too!
I have a solution, but it's not the best one. Let's transform our code a little.
Now, DBI will bind the parameter as if it was an integer, not a varchar.
The better solution, imho, would be for DBD::mysql to go ahead and look up the column type and bind appropriately. However, I am aware that there are potential secury concerns, so it may be appropriate for an additional option to be passed, maybe "mysql_discover_bind_type", that would always default to 0. Then, those of us who want it would have to explicitly turn it on, but those who don't know enough would turn it off.
Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose
I shouldn't have to say this, but any code, unless otherwise stated, is untested