Best technique to make node mysql run fastest?

Question

I am using this

var mysql = require('mysql');

in my node.js app. I am interested to make my app perform the fastest. I have many functions that connect to SQL. There is 2 approaches I am familiar with

For every request, I make a new connection and then execute the query and the close the connection.
Open the connection and make it a global variable, and then never close it. Then for every request that comes in, it just uses the opened connection saved globally.

Which is generally better to use? Also for number 2, if the server closes unexpectedly, then the sql connection doesn't close. Is that bad?

Thanks

Have you seen the answers at [node.js + mysql connection pooling](https://stackoverflow.com/questions/18496540/node-js-mysql-connection-pooling/18496936)? — Andrew Morton, Mar 20 '20 at 20:10
number 2., couldn't it be replaced by a connection pooling solution? — lainatnavi, Mar 20 '20 at 20:11

score 1 · Accepted Answer · answered Mar 20 '20 at 20:20

Approach 2 is faster, but to avoid the potential problem of connections dropping without unexpectedly, you'll have to implement testing mechanism for every segment that queries the database (ex: count the number of returned rows).

To take this approach further, you can define connections bank or pool. Where you can deal with connection testing and distributions. The basic idea is to have many connections to the database and just inject healthy connections to consumers (functions, or objects that query the database). As Andrew mentions in the comments You can check this question: node.js + mysql connection pooling

Since the database is an essential asset to a project, if this is not a homework or learning project, it might not be a bad idea to explore 3rd party libraries, where a lot of the connections and security details is covered and automated.

Best technique to make node mysql run fastest?

1 Answers1