MySQL and JavaScript – One Connection vs Multiple Connections

client-relationsjavascriptMySQLnode.js

On nodejs with node-mysql, should I create one connection for the service like this:

var client = mysql.createClient({
    user: 'user',
    password: 'pass'
}); // Create a new mysql Client
io.sockets.on("connection", function (user)) // when a user enters
{
    result = client.query('check user'); //check for user on DB
    if (result) {
        user.on('do stuff', function (data) // set to do something on ask
        {
            client.query('some stuff'); //run the mysql client to work
        });
    } else {
        user.disconnect();
    }
});
}

or or for each user like this:

io.sockets.on("connection", function (user)) // when a user enters
{
    var client = mysql.createClient({
        user: 'user',
        password: 'pass'
    }); // Create a new mysql Client when the users enters
    result = client.query('check user'); //check for user on DB
    if (result) {
        user.on('do stuff', function (data) // set to do something on ask
        {
            client.query('some stuff'); //run the mysql client to work
        });
        user.on('disconnect', function (data) {
            client.close();
        });
    } else {
        user.disconnect();
        client.close();
    }
}

What I'm asking is:

  • is the first one faster because the connection is always open and on
    the second one it needs to create every time a user connects?
  • is the first one slower because if many users are asking for queries
    he only does one at the time, and on the second one each one have his
    own client and can make multiple connections at the same time?
  • is the second one heavier for the node server and for the mysql
    because it needs to have 1 client for each user?
  • is the first one heavier because it never closes the mysql connection, and after some queries may have some memory leaks?
  • or should I create and close a mysql connection for every query?

PS: the application connected to the node server will have a big number of users, connected during a long period of time, but will have a very low number of queries, but the users probably will ask for queries at about the same time.

Best Answer

Node is a single-process, single-threaded, asynchronous, event-driven application. So only one request/event is handled concurrently.

Since node-mysql looks like its API is synchronous, you should be fine with a single connection. This assumes that your application doesn't rely on SQL session variables, temporary tables, or transactions that might span multiple Node events.

Related Topic