MySQL pool in Hyperf: do prepared statements survive across coroutines?
Trying to understand prepared statement reuse in Hyperf with the pooled MySQL connection. Under PDO on FPM a prepared statement is bound to one connection. In Hyperf each coroutine borrows a connection from the pool and returns it, so a statement I prepared in one coroutine is meaningless to the next one that gets a different connection.
Is there any server-side prepared statement caching benefit in this model, or am I effectively re-preparing on every request? And does the framework do anything clever here, or should I just stop thinking about prepared statement caching entirely under a pool?
A prepared statement handle is tied to the specific connection that prepared it, full stop. In a pool you cannot rely on getting the same connection back, so you cannot reuse a handle across requests. What you can rely on is that the parameterized query still gives you the security and the plan-cache benefits on the server side for the duration you hold that one connection. Stop thinking of the PHP-side handle as a long-lived cache, it is per-checkout.
The server-side benefit is smaller than people think anyway. MySQL prepared statement caching is per-connection, not global, so even with persistent connections each connection has its own cache. The real win of prepared statements in your model is correctness and injection safety, not shaving microseconds off planning. Treat the performance angle as a rounding error and the safety angle as the actual reason.
If you genuinely have a hot query where planning cost matters, the lever is not statement handles, it is keeping the query shape stable so the server plan cache stays warm on each connection. Avoid building dynamic IN lists with variable counts, since each distinct count is a different statement and blows the cache. Normalize to a fixed shape or use a temp table for big lists. That helps far more than chasing handle reuse.
That dynamic IN list point is a real one for us, we have a query whose placeholder count varies by request and it never benefits from any cache. Switching it to a fixed-shape query with a JSON array parameter stabilized it. And good to have it confirmed that the handle is strictly per-checkout, I was about to build a pointless statement cache keyed by SQL on top of the pool.
Worth adding: do not enable PDO emulated prepares thinking it helps here. With emulation off you get a real round trip to prepare, with it on the driver interpolates and sends one query. Under a pool with short checkouts, emulated prepares can actually be fewer round trips for one-shot queries. Benchmark your specific workload, the default is not always the faster choice in a coroutine pool.
```php blocks are runnable.