Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

DBD::mysql and binding placeholders

by dragonchild (Archbishop)
on Sep 22, 2004 at 15:51 UTC ( #392941=perlmeditation: print w/ replies, xml ) 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

create table foo ( bar1 Int ,bar2 Int );
with a million rows, indexed properly, and you do the following in Perl.
my $sth = $dbh->prepare_cached( "SELECT bar2 FROM foo WHERE bar1 = ?" +); $sth->execute( 2 );

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:

SELECT bar2 FROM foo WHERE bar1 = 2;

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

SELECT bar2 FROM foo WHERE bar1 = '2';

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.

my $sth = $dbh->prepare_cached( "SELECT bar2 FROM foo WHERE bar1 = ?" +); $sth->bind_param( 1, 2, SQL_INTEGER ); $sth->execute();
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.

We are the carpenters and bricklayers of the Information Age.

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

Comment on DBD::mysql and binding placeholders
Select or Download Code
Re: DBD::mysql and binding placeholders
by mpeppler (Vicar) on Sep 22, 2004 at 16:26 UTC
    This is indeed a common problem, and doing the lookups "manually" in the driver can be difficult.

    DBD::Sybase will do the right thing for "normal" placeholders - i.e. for plain SQL statements. This is because the Sybase protocol performs this discovery for you, and so DBD::Sybase knows what type any parameter is (as an aside, this is also why the WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL)) construct does NOT work with Sybase - there is no way to determine the type of the second placeholder!).

    For exec some_proc ? statements DBD::Sybase works like MySQL. It would be possible to look up the type of each parameter, but it's an overhead that I didn't want to code into the driver. Hence DBD::Sybase will assume SQL_CHAR unless told otherwise.

    The difference with MySQL is that Sybase won't accept a CHAR string where an INT/NUMERIC/FLOAT is expected, and throws an error - so at least you wouldn't be left wondering why it is slow... :-)


      I'm pretty sure that DB2 will also choke if your SQL has a type mismatch. At least it does when accessing it directly; not sure if DBD::DB2 massages things in any way.


      DBD::Ingres tries to guess by looking at the first bound variable - if it looks like an integer, it is bound like an integer, otherwise as a varchar.

      Not foolproof, but it DWYM most of the time, and in the few times where it fails, you get an error, and have to bind with an explicit type.

Re: DBD::mysql and binding placeholders
by Anonymous Monk on Sep 22, 2004 at 18:07 UTC
    Hmm, If I execute it in the mysql console, I see no difference at all (i have over 5 million rows) whether I query for ..'digit' or digit. Are you sure your results weren't cached?
      No difference as in both being fairly fast or slow? Is there an index on the field? Does MySQL have a way to print out the query plan to tell you if an index is being used?
      I was describing a small test case, and a few million rows on a strong machine may not notice a difference. The actual query was comparing strings against BIGINTs in two tables, then joining them. The MySQL manual discusses this as well, which is why I realized my mistake once I saw what DBI->trace() said was actually happening.

      We are the carpenters and bricklayers of the Information Age.

      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

Re: DBD::mysql and binding placeholders
by tantarbobus (Hermit) on Sep 23, 2004 at 02:36 UTC

    You are going to have to wait until mysql 4.x and DBD::mysql gets the real prepared statements. Once you get those, it will automatically look up types at prepare time (except for LIMIT stmts which you can't prepare server-side just yet).

    Although, your mysql_discover_bind_type will probably go into the 2.9x tree because, I think, we are going to be removing the LIMIT ?, ? placeholder autodetection from the 2.9x code as there were just too many cases (bugs), and the code was getting too ugly trying to support it.

    If you pester me over the weekend (I am slammed until friday afternnon), I'll add it for you. Of course (good) patches are always welcome. Also note that you can:

    my $sth = $dbh->prepare($statement); $sth->bind_param(1, undef, SQL_INTEGER); # bind undef. for (1..$n) { #no need to rebind. $sth->execute($value); }
      I found this in a search looking for something else, but seeing how old this is I am curious. Can anyone comment on whether this is still a problem now (i.e. mysql 5.x ...)?

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://392941]
Approved by Old_Gray_Bear
Front-paged by Old_Gray_Bear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (6)
As of 2015-03-29 09:58 GMT
Find Nodes?
    Voting Booth?

    When putting a smiley right before a closing parenthesis, do you:

    Results (630 votes), past polls