CODEWITHSUNDEEP
phpmysqldatabaseoptimization
mculp
mculp

Reputation: 2687

How often should I close database connections?

Currently, I'm opening a database connection in my app's initialization. It's a fairly small app, PHP if that's relevant.

Should I be connecting to the database, making calls, then closing and repeating this process for each database function I write?

For example, I have the following function which grabs the $db variable from my app's initialization.

function get_all_sections()
{
    global $db;
    $sql = 'select * from sections'; 

    if (!$db->executeSQL($sql, $result))
    {
        throw new Exception($db->getDatabaseError());
        exit();
    }

    $sections = array();

    for ($i = 0; $i < $db->numberOfRows($result); $i++)
    {
        $sections[] = new Section($db->fetchArray($result, MYSQLI_ASSOC));
    }

    return $sections;
}

Would it be better if I opened the connection then closed it after I fetched the rows? That seems like a lot of connections that are opened and closed.

Upvotes: 7

Views: 6294

Answers (5)

LukeP
LukeP

Reputation: 10422

The simplest solution would be to use mysql_pconnect() - see here

This way if the connection is already open it will use it instead of creating a new one. If it's not it will connect again

Upvotes: 1

cdeszaq
cdeszaq

Reputation: 31320

Since PHP MySQL connections are fairly light-weight, you are probably OK opening and closing the connection when needed. The same is not true of other database connections, such as connecting to SQL Server, which has rather heavy connections.

In all cases, however, do whatever makes the most sense in terms of maintainability/logic/dependencies. Then, if you find you are feeling a measurable slowdown, you can optimize those areas that need the speed-boost.

When in doubt, follow the golden rule: Don't optimize prematurely.

Upvotes: 3

nont
nont

Reputation: 9529

Use mysql_pconnect for connection pooling, and close at the end of each operation. The thread won't really close, and the thread will be re-used. This way its both safe and efficient.

Upvotes: 3

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799550

Connecting to the database takes a finite amount of time. It's negligible when connecting over a domain socket or named pipe, but it can be much larger if over a network connection, or worse yet, the open Internet. Leave it connected for the life of the request at least.

Upvotes: 3

mr-sk
mr-sk

Reputation: 13427

If you have connection pooling on (http://en.wikipedia.org/wiki/Connection_pool) its ok to be grabbing a new connection when you need it. HOWEVER, I'd say to be in the habit of treating any resource as "limited" and if you open the db handle keep it around for as long as possible.

Upvotes: 5

Related Questions