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
);
[download]

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 );
[download]

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;
[download]

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';
[download]

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();
[download]

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.

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.