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?

+2  A: 

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.

I thought it would be better to release the memory for each query everytime I call it. thereby using less memory, just more times.
Mr Fish
@Mr Fish: It depends how you have implemented the mysql class, yes if you need one connection, no need to create more.
Instantiating the connection only to close it is wasted processing cycles. You're leaving that memory open momentarily only to be used again and at the expense of the system to re-instantiate it.
Dan Heberden
+3  A: 

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

+3  A: 

Should I open a connection once?


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.
Col. Shrapnel
Connect uses memory. That's why most of the database libraries can tell you immediately if there's a connection error-- they actually attempt to make the connection. There's memory being utilized on both the client and server side for that connection attempt, and subsequent connection. Connections aren't magical things that use no memory. The memory about is negligible, but it uses memory. PDO is an exception because it doesn't actually have a connect function/method, but you still use memory by just creating a PDO object and not connecting.
The memory about is negligible. That's all.
Col. Shrapnel
+3  A: 

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:

-- HTH

Pete Wilson

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 ;-)

+1  A: 

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.

le dorfier
Suggest a good MySQL abstraction layer then. I'm developing my own application framework using a loose interpretation of MVC. I'd love to make it so I can switch source DB's per different client via a good abstraction layer.
Mr Fish
PDO is baked into the language. ADODB has been around forever.
le dorfier

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');