18

Is there any reason why I should close the connection after a query or at the end of the script?

What are the benefits of doing/no doing do?

Dharman
  • 30,962
  • 25
  • 85
  • 135
bear
  • 11,364
  • 26
  • 77
  • 129
  • If you are talking about PHP scripts, may I suggest adding that tag to your question? – Bill Karwin Aug 29 '09 at 15:12
  • Dupe: http://stackoverflow.com/questions/880885/close-mysql-connection-important and related: http://stackoverflow.com/questions/336078/php-mysql-when-is-the-best-time-to-disconnect-from-the-database – karim79 Aug 29 '09 at 15:18

2 Answers2

31

The connection (if not persistent) is always closed at the end of the script, so, in theory, you don't need to close it yourself.

Still, if your PHP script takes lots of time to execute, it's a good idea to close the connection when you don't have to do any request to the database anymore -- at least, if the long calculations are done after the queries.

This is especially true if your application is deployed on a shared hosting : your user account can generally only have a few connections opened at the same time. (That number of simultaneous opened connections can be pretty small on shared hosting ; it's generally bigger on private servers).


The reason we often don't close connections ourselfves is :

  • we generally don't really know when we have done all our queries -- this is especially true with pages that are made of lots of small "blocks" ; each one of those is independant from the others, and can do queries on its own ; so, when can we close the connection ?
  • web pages are generally quite fast to generate, so we don't really bother about closing the connection to DB.
Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
  • 29
    Every time you don't close your connections, a DBA kills a kitten. – Eric Aug 29 '09 at 15:15
  • 3
    Every time I don't close a connection, PHP does the work for me :-p PHP saves the kittens ! ;; actually, what I said with page made of blocks is quite true : think about any CMS, as example : when do know know the connection should be closed ? At the end of the execution of the PHP code (not before, as some "blocks" might still need to do some SQL query) ;; so, closing the connection by hand is pretty useless... ;; except, of course, when you are generating something that takes time and doesn't do any DB query, like a PDF, or something like that. – Pascal MARTIN Aug 29 '09 at 15:18
5

The benefit is if you're going to be doing long-running processing but have finished querying the database then there is no point holding open a connection. The same goes for holding the user session open (which blocks other requests).

An example of this might be creating a large PDF report. This might take you 20-30+ seconds to create and write out the file but you get all the data you need in the first second.

Ordinarily however you may as well do it automatically (assuming the connection isn't persistent).

cletus
  • 616,129
  • 168
  • 910
  • 942
  • 1
    What is persistent connection? `mysqli(MYSQL_HOSTNAME, USERNAME, PASSWORD, DATABASE);` is persistent? – mcan Jun 22 '14 at 15:04
  • Unlike the mysql extension, mysqli does not provide a separate function for opening persistent connections. To open a persistent connection you must prepend p: to the hostname when connecting. – Tarik Aug 09 '17 at 19:05