Using PDO::MYSQL ?
I've recently discovered a few things about how the mysql client library does things that seem a bit silly to me, so I'm going to share them with you.
- native prepared statements cannot take advantage of the query cache, resulting in lower performance.
- native prepared statements cannot execute certains types of queries, like "SHOW TABLES"
- native prepared statements don't correctly communicate column lengths for certain other "SHOW" queries, resulting in garbled results.
- calling stored procedures multiple times using native prepared statements causes the connection to drop.
I recommend that you use the following attribute when working with PDO::MYSQL, available in the current PHP 5.1.3 release candidates and snapshots:
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
This causes the PDO native query parser to be used instead of the native prepared statements APIs in the mysql client, and effectively eliminates those problems.
I'll admit that the last point could well be a bug in my code; since I'll be at the MySQL Users Conference next week, I should be able to sit down with the right people and fix it.
Wez Furlong in
PHP.
Would you like to work
with me?
I have positions open for server/infrastructure software development (C) and QA.
#
Just for completeness here is the list of queries supported in the prepared API
"The following SQL statements can be used in prepared statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. Other statements are not yet supported."
Note for the bulk of the show command you can use the INFORMATION_SCHEMA starting with mysql5, though performance is subpar there ..
ANyways, in MDB2 I do not go through the prepared API at all when using query() and exec(). Maybe that is the way to go here too, especially since you cannot set parameters anyways and so preparing will actually just add overhead?
As for the query cache, I can see their rational for not sticking the queries into the query cache, but I guess they should support it somehow to better accomodate stateless computing setups (their main user base).
#
So, native prepared SELECT statements do not use the query cache. But I'm guessing prepared statements are still the way togo with INSERT, UPDATE, & DELETE.
CALL/EXEC not being listed as supported by the prepared api, seems to indicate the problem is with mysql client, atleast.
#
So is there much of a performance hit for using emulated prepared statements rather than letting the MySQL engine prepare them (for vanilla SELECT, INSERT, UPDATE, DELETE statements)?
Also, do you see any change in your above recomendation (to use emulation) in the foreseeable future? (that is, do I need to put it on my list of "behavior might change- keep checking back" items?)
use emulated prepares for the foreseeable future
The native prepared statement API is still quite young it seems. I was considering making emulated prepares the default, but it seemed like a pretty drastic change to make for a minor point release.
I'm going to talk with the mysql folks next week to see what they're planning on that front.
In the meantime, use emulated prepares as they are faster and more reliable.
#
From one end-user's perspective, it would actually make more sense to have the emulated behavior be the default if it's faster, more reliable, and allows the query cache to operate.
99% of us average users are going to want the emulation for the stated reasons and it saves having to do the setAttribute() call wrapped in a version_compare() call.
Make the deviants do the work and let the rest of us live in semi-ignorant bliss.
I respect the whole point release and change thing, but I have to wonder who, if anybody, is REALLY going to be negatively effected by a change if they notice at all?
Information schema
Lukas, SHOW commands are built on top of Information_schema tables. The only one which has duplicated code is SHOW PROCESSLIST vs. SELECT FROM I_S.PROCESSLIST (5.1.7+). But MySQL is lacking internal data dictionary, which is a drawback when you have thousands of tables and you use either SHOW or I_S.
HEAD
Is there a reason why this is not in HEAD?
too busy for HEAD
I've been so busy recently that I've focused what time I have had on the current release branch only.
Thanks a bunch !
I was hitting a repeated problem with some import queries (General error 2030) and had begun to get pretty much annoyed with these working on every testing machine I had while totally failing on my production system.
Your suggestion saved my brains, most of my afternoon and maybe my job ! Muchas Gracias !