Re: mod_perl was hosed by a bind variable

Something in the back of my head is saying that DBD::MySQL does not truely support SQL placeholders, and instead emulates them. So it doesn't surprise me that you saw better performance by not using them.

After tooling around the net for a little while, I didn't find anything more conclusive on DBD::MySQL placeholders being emulated or not. I know the older versions of DBD::MySQL didn't support it. Here is a link to that; Tim Bunce talking about DBD::MySQL, look in the PARAMETER BINDING section for this ...

Neither engine supports placeholders, but the DBD::mysql and DBD::mSQL drivers provide full emulation. Question marks are used as placeholders, as in ...

I also found this more recent link on the DBI Dev message board. They are talking about named placeholders, but in it this person mentions that ...

DBD::mysql does the placeholder parsing in the DBD driver so you can substitue a placeholder for anything, including the whole of the SQL statement if you so desire (with a little bit of trickery (such as binding as an integer)).

I would be very interested to know for sure who/where/what does the placeholder parsing with DBD::MySQL. And if it would really make a difference anyway.

-stvn

Comment on Re: mod_perl was hosed by a bind variable

Replies are listed 'Best First'.
Re: Re: mod_perl was hosed by a bind variable by dws (Chancellor) on Jan 29, 2004 at 23:20 UTC
MySQL 4.0x doesn't support prepared queries, so they're emulated. Emulation means hanging on to the query, and expanding the '?' markers at `execute` time, substituting appropriately quoted values. However, this isn't a heavyweight operation, particularly not when compared with the overhead of shipping the query over the wire to the server, and waiting for the server to crank on it and return a result. Assuming (and we have to assume, since we haven't seen code yet, and what we have been shown doesn't verify that results are identical) that query that arrives at the database server process is identical in either case, and assuming that a standalone (non-mod_perl) test doesn't show the the same disparity in timing, then something underneath `bind_param` is interacting with mod_perl. On a light read of DBD.pm and DBI::MySQL.pm, I can't see what that would be. Curious.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Re: mod_perl was hosed by a bind variable
by dws (Chancellor) on Jan 29, 2004 at 23:20 UTC

execute

Assuming (and we have to assume, since we haven't seen code yet, and what we have been shown doesn't verify that results are identical) that query that arrives at the database server process is identical in either case, and assuming that a standalone (non-mod_perl) test doesn't show the the same disparity in timing, then something underneath bind_param is interacting with mod_perl. On a light read of DBD.pm and DBI::MySQL.pm, I can't see what that would be. Curious.

[reply]
[d/l]
[select]