How to increase mysql speed on node.js?

Question

MySQL

Hello.
Save speed issue in mysql on node.js.
Installed the following extension - https://www.npmjs.org/package/mysql .
There is a code:

var mysql = require('mysql'),
  post_term = {
    "name": 'test',
    "second_name": 'test',
    "third_name": 'test'
  },
  time = 0,
  start_time = 0;

var connection = mysql.createConnection({
  host     : '***',
  database : '***',
  user     : '***',
  password : '***'
});

start_time = Date.now();

connection.query('INSERT INTO terms SET ?', post_term, function(err, result){
  if(err)
    throw err;

  time = Date.now() - start_time;
  console.log('Time: '+time);
});

The running time for this code on my laptop is 370 milliseconds.
A similar code in php works out in 0.000133283 milliseconds.

<?php

  $link = mysql_connect($host, $user, $pass);
  if (!$link) {
      die('Не удалось соединиться : ' . mysql_error());
  }

  $db_selected = mysql_select_db($db_name, $link);

  if (!$db_selected) {
      die ('Не удалось выбрать базу: ' . mysql_error());
  }

  $start = microtime(true);

  $query = "INSERT INTO terms (name, second_name, third_name) VALUES ('test', 'test', 'test')";
  $result = mysql_query($query, $link);

  if (!$result) {
    die('Неверный запрос: ' . mysql_error());
  }

  echo number_format(microtime(true) - $start, 6);
?>

What am I doing wrong? Why does node take so long to run?
The correct running time of the php script is 138.12ms.
An error occurred in the calculations due to the fact that the microtime values ​​​​(in php) were taken in microseconds, and not in seconds.

Answer 1

Because we read the "Establishing connection" section of the package documentation.
In your case, the connection will be established not at the moment createConnection is called, but at the moment the first request is executed, therefore 370ms is not a "clean" request time.
The documentation has an example with a forced connection.

Answer 2

In addition to the problems already discussed above, to speed up it is useful to look at what mysql.createPool is, it is useful for the asynchronous execution of competing requests so that they do not go through one connection and do not crowd. Also take a look at the cluster module, for multi-threaded launch of the node, if you have requests to the database during HTTP requests, then they can be parallelized into several cores, and by default everything is processed on one core. It would not be superfluous to think about caching query results in RAM, or even move from executing queries to the database from HTTP requests, to their preliminary and deferred execution. That is, at startup, take as much data as possible into memory at once (now there is a lot of memory and it is cheap), on HTTP requests, do a hash search instead of a database query, and when changes are made, do not make a request immediately, but write the change to a special queue and save it to the database in lazy mode. So speed up by several orders of magnitude. If the data does not fit in memory, then do not use pre-reading, but do only lazy writing. And my liba may also come in handy, well, this is for convenience, and not for speed:https://www.npmjs.org/package/mysql-utilities In general, I advise PgSQL or MongoDb, they are much faster and more convenient, I don’t use MySQL anymore and I’m glad about it.

Answer 3

Perhaps php is establishing a connection at the socket level, and the node climbs through the entire stack.
Try setting the socketPath for the connection.