Opening one MySQL connection vs opening and closing a lot of connections?

Question

I have a > x20000 loop in php that checks if an entry exists in a MySQL database, I open & close the connection for every entry in the loop, but a friend told me that's crazy and I should open one connection, run the loop and then close it, but he didn't tell me why. Could someone explain me the benefits of reusing the same connection? is it CPU usage or what?

Answer 1

It is the network connection + mysql connection overheads. It takes a time to connect and to be clear it is "expensive" operation.

Answer 2

Not only is it expensive, but if you have multiple requests coming in for the script that are doing it simultaneously, you could end up bumping up against the connection limit on the server, such that further requests must wait or are denied.

But the main thing, as @zerkms said, is that it's an expensive operation. About six months ago, I took a script that made repeated connections in a loop and moved the connection outside the loop, and the script's execution time dropped from 10-12 seconds to well under 1 second.