Best placement for connection strings - MySQL on Linux/Apache
Question: is it better to surround a query string and related processing with open and close connections strings, or is it best to open the connection at the beginning of a lengthy script and close it at the end of the script? I ask this in terms of coding efficiency, resource utilization of the server and any potential security risks. Thanks.
If you will be doing multiple queries within the same script / within a short period of time, the best thing to do is open the connection at the beginning, and close it at the end.
There are two reasons for this: Connections are expensive. There is tremendous overhead (compared to running queries) with setting up the connection, opening it, etc. This will slow down your script's execution considerably. Second, it reduces the amount of work on the database side.
If, on the other hand, you're just doing a single query and then a bunch of processing based on that, it doesn't hurt to close the connection right away (assuming you've gotten all your data), to free up the connection for other resources.
I'm not sure about the overhead being "tremendous", but I guess that's platform- and configuration-dependent, along with how many milliseconds you consider to be "tremendous." That being said, there is an overhead, so I agree that in the vast majority of cases I would opt for one connection at the start and not closing until I'm done with DB access.
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation