What are the implications of opening MySQL connections over and over again in PHP

Question

Specifically, I have a DB class that opens and closes multiple MySQL connections every time I call the Query function in the class. Should I open a connection once? or is it ok to open and close connections like this?

Answer 1

Should I open a connection once? or is it ok to open and close connections like this?

You should open multiple connection only when needed otherwise it is not a good idea to open multiple connections thereby consuming a lot of memory which is an overhead.

Answer 2

If you don't want to change much instead of mysql_connect() use mysql_pconnect() This way you will use the opened connection. Bu I would agree with @Sarfraz Ahmed - use it only once

Answer 3

Should I open a connection once?

No.

I thought it would be better to release the memory

Actually, connect itself do not consume memory.
And - most important part - you should not worry of such imaginable things.
Don't make decisions on based on empty assumptions.
Here is 2 simple rules to follow:

1. When you don't know, what to do, do it most general way, as everyone does.
2. Do necessary things only. Don't try to foresee every problem in the future. Deal only with present problems, not with imaginable ones. Premature optimization is the root of all evil, as it said.

Answer 4

My simple-minded (ISAM, no transactions) C-language app runs for eight hours a day, updating multiple tables in one database over one single MySQL connection that stays open the whole time. It works just fine. Anytime there's any kind of MySQL error (not only server gone away), the code just calls mysql_real_connect() again and it picks right up without any trouble. Reconnection is one of the places where, in my opinion, MySQL functions flawlessly.

But there's plenty of controversy and discussion about the goodness/badness of persistent connections. You can find some of it here:

http://www.google.com/webhp?hl=&sourceid=navclient-ff&rlz=1B3GGLL_enUS384US384&ie=UTF-8#rlz=1B3GGLL_enUS384US384&hl=en&source=hp&q=mysql+persistent+connection&aq=0&aqi=g4g-m5&aql=&oq=mysql+persistent+conn&gs_rfai=Ch2c6iCchTO3zG4i6MZ-i7JAOAAAAqgQFT9BAKCs&fp=ff274912d96214e6

-- HTH

Answer 5

conecting also uses cpu time. so if you reconnect about 8 times per page, and you have somewhat about 100 visitors a day wich call up 5 pages in average you have 8*100*5=4000 reconnects in one day. and thats a small website. you should realy think about connecting only once or when the connection is getting lost. that would somehow lower your electricity bill also ;-)

Answer 6

In general, go back to the simplest MySQL tutorial you can find and do it just that way. Don't deviate unless you have a problem you are trying to solve.

MySQL works just fine when you keep it brain-dead simple. Don't add complexity.

BTW, are you writing yet another MySQL abstraction layer? Why? This question is a good example why reinventing a wheel can be risky.

Answer 7

I think you should use a Registry Object to open your Database Connection, and make your Database Object a singleton.

Registry::Set('DB', new Database());
$DB = Registry::Get('DB');