Analyzing Slow Query Issues Caused by PHP PDO Prepare Behavior

Everyone knows that a good slow‑query system is essential for quickly diagnosing performance problems; a database management platform with an effective slow‑query subsystem essentially holds the key to unlocking performance issues.

The symptom observed was a large number of slow‑query entries in MySQL logs that contained no actual query text, a situation never seen before in the author's experience.

Investigation

To reproduce the issue, three approaches were tried:

Simulating PREPARE with the MySQL client.

Simulating PREPARE using a Perl script.

Simulating PREPARE with PHP (PDO).

Both the MySQL client and Perl simulations showed the same behavior: the administrator command Prepare was not visible in the slow‑query log.

The PHP simulation, however, produced the expected slow‑query entry, confirming that PHP could reproduce the phenomenon.

Next, the full SQL execution trace for the same session ID was captured by enabling MySQL's slow‑query capture mode (slow=0) and analyzing the packet dump.

The analysis revealed that the PREPARE phase took a long time, while the actual query execution was fast, which was unexpected.

Understanding PHP‑PDO Prepare Modes

PHP‑PDO supports two prepare modes:

Local (emulated) prepare – the statement is prepared on the client side and never sent to the MySQL server: $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

Server‑side prepare – the statement is sent to the MySQL server for preparation: $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Official documentation: http://php.net/manual/zh/pdo.prepare.php

Verification with strace showed the difference clearly:

In server‑side mode, the PREPARE request includes the full query and placeholders, which can be seen in the trace.

In local (emulated) mode, the PREPARE request does not send the query to the server; only the EXECUTE step transmits the statement.

The business team was using the server‑side mode (by setting ATTR_EMULATE_PREPARES = false ) to reduce perceived overhead, but in this workload—frequent opening and closing of connections—server‑side preparation added unnecessary latency and made slow‑query debugging difficult because the real query text was hidden.

Documentation also notes that prepared statements can degrade performance under high concurrency.

Adjustment and Validation

To confirm the change, the attribute was set back to emulated prepares and the slow‑query logs were observed to return to normal, indicating the adjustment took effect.

Summary

Advantages of prepared statements : Prevents SQL injection. Can improve performance in scenarios with many repeated executions of the same statement with different parameters.

Disadvantages : Server‑side preparation incurs overhead, which becomes a bottleneck under high concurrency. When using server‑side prepares, the actual query may be invisible in slow‑query logs, complicating troubleshooting. For workloads with frequent connection churn, emulated (local) prepares are preferable.

Final Recommendations

Use PDO's default configuration (emulated prepares) to retain SQL‑injection protection while avoiding extra server load.

Switch to server‑side prepares only after thorough testing and when the workload clearly benefits from it.