当在代码中作为预备语句运行时，MySQL查询在PHP中运行缓慢，但可通过直接MySQL查询快速运行

Question

A PHP application I’m trying to debug runs several badly designed queries on a bigger MySQL database.

A few pages are really slow and it turned out that it is because of a few queries. I started to check every query one by one and though they are slow they are not that slow on their own.

After some further debugging it turned out that they are only slow when they are being run by the application as prepared statements.

• If I run the query by hand via MySQL client it takes about 300 ms. If I run create a prepared statement via MySQL client and set the parameters and run it, it takes about 300 ms.
• If I run the simple query from PHP (mysqli) it takes about 300 ms.
• If I run it like the application does—via mysqli—as prepared statement it takes 100 seconds.

I thought maybe it's mysqli so I tried it with PDO, the result is the same. Tried different PHP versions (5.6, 7.2, 7.3) and get the same result.

So I gave a last chance and wrote a small Go script to test, and I get the same results and things improved.

Now if I run the prepared statement version of the query from MySQL client or MySQL Workbench or PHPStorms Database client it's fast. And if I run the query from code it's freaking fast.

Any help would be really appreciated about what should I look after, where should I continue my debugging.

Answer 1

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.