I'm currently trying to fix an issue with our production server not being able to handle SQL queries.
Looking at the process list, MySQL is taking 120 seconds plus to complete processes that are running queries, that when I run them myself through Heidi, are completing in less than a second. So, why would queries that are being processed coming from PHP take significantly longer (and in most cases timing out) than when the same query goes through straight away from HeidiSQL?
You are probably using a persistent connection, and it can cause such problems, in case the previous PHP code that used this connection had been stopped in the middle and never ended.
read more here: What are the disadvantages of using persistent connection in PDO
Turns out the problem was that the server where PHP was running (on a different hosting provider as we're migrating to cloud) had a throttled network connection and was unable to handle all of the data being sent back from MySQL. Turning on caching on the PHP side solved the problem.
