Why Are Certain Types of Prepared Queries Using Pdo in PHP with MySQL Slow

MySQL queries run slow in PHP when run as prepared statements in code but run quickly via direct MySQL queries

So as it turns out, this was caused by a slightly different execution plan. MySQL seems to create the execution plan purely based on the statement, not including the parameter values when using prepared statements via mysqli or PDO , which kind of makes sense. However when it's provided with the full query, in our case it introduced an optimisation on one of the tables which made a huge difference.

One of the tables (with 5.5 million rows) had the Using join buffer (Block Nested Loop) Extra on it when running with the non prepared statement, while with the prepared statement it didn't. This seems to have made a close to 1000x performance difference for us.

I am still not sure why this has not an issue via PHPStorm or the CLI mysql client, my best guess is, that certain APIs in MySQL expect the execution plan to be complete when a statement is prepared, while other APIs, and the CLI client don't.

PHP - PDO: MySQL's logged prepared statements include PS data

There are two possible explanations for what you are seeing in the log.

• an obvious one, by default PDO just emulates prepared statements, sending the regular SQL query to the server.
• in case the real prepared statement has been used, the log entry you are seeing has been artificially conjured especially for the logging purposes, while prepared statement has been executed properly, with data sent separately.

So, just make sure that emulation mode is turned off, that you are binding your LOBs properly, and don't worry

PHP PDO: How long are prepared mysql queries cached?

A prepared query is given to the SQL server, which parses it and possibly already prepares an execution plan. You're then basically given an id for these allocated resources and can execute this prepared statement by just filling in the blanks in the statement. You can run this statement as often as you like and the database will not have to repeat the parsing and execution planning, which may bring a speed improvement.

As long as you do not throw away the statement, there's no hard timeout for how long the statement will "stay prepared". It's not a cache, it's an allocated resource on the SQL server. At least as long as your database driver uses native prepared statements in the SQL API. PDO for example does not do so by default, unless you set PDO::ATTR_EMULATE_PREPARES to false.

At the end of the script execution though, all those resources will always be deallocated, they do not persist across different page loads. Beyond that, the SQL server may or may not cache the query and its results for some time regardless of the client script.

PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Why is my PDO Mysql database connection closing slow?

The query I was executing simply took too long and was still running on the background while the php parser continued.

Using PDO Prepared Statements With MySQL Query Variable

You can't use query parameters to insert expressions to your syntax. Parameters are not just string-interpolation. If they were, there would be no benefit to using them, because you can do string-interpolation easily in PHP already.

The whole point of query parameters is that the value is combined with the query on the server, after the SQL syntax has been parsed, so it's too late for you to insert any new syntax, like an expression.

Query parameters are always treated as a single scalar value. You can't use a parameter for:

• Table identifiers
• Column identifiers
• SQL keywords
• Expressions
• Lists of values

As others have explained, in this case, you have no need to use a query parameter anyway. Using the literal expression log + 1 directly in your query is safe. There's no untrusted content (from users or other sources) being inserted into the query, so there's no risk of SQL injection.

Do MySQL prepared queries provide a performance benefit for once-per-session queries?

No. PHP is a "shared nothing" architecture, so every resource associated with one request (one page view) is discarded at the end of that request. Prepared queries are not available to a subsequent database connections.

The scenario in which you would get benefit from a prepared query is when you prepare it and execute it many times during the same PHP request.

---

Related Topics