CODEWITHSUNDEEP
mysqlsqlnode.jsstreamstreaming
Diveesh Singh
Diveesh Singh

Reputation: 43

Using the mysql module in node.js for large tables

I have a large table in my MySQL database (about 10 million rows), and I need to get all this data into JSON format. For smaller tables, I would use the basic connection.query("SELECT * FROM TABLE, function(err, results) {}); syntax. However, I don't want to have to load the whole table into memory.

I noticed that the mysql module had the ability to "stream" rows (https://github.com/felixge/node-mysql/#streaming-query-rows), so I was wondering whetherthat still loads the entire table into memory and then just gives us each row one by one, or whether it actually only loads one row at a time, so the whole table is never stored in memory at once.

Upvotes: 4

Views: 5109

Answers (3)

Divek John
Divek John

Reputation: 755

Too late to answer but for someone who needs an optimized less time-consuming solution in 2021

All the above solutions are good but have

  1. Time complexity O(n)
  2. Hign Storage complexity O(n) or Hign Memory usage problems sometimes app may crash because of too many requests

Solution: Maintain a synchronized JSON file when a user does CRUD operations to DB for example in a put request

app.put('/product/:id', (res,req)=>{
    // step 1 do update operation in db
    // step 2 do update operation in JSON file

    return res.send('OK 200')

})

So next time when a user request JSON user can instantly get the JSON file

Happy coding :)

Upvotes: 0

Alexander R.
Alexander R.

Reputation: 1756

Load your data by chunks. Here some working example.

var mysql = require('mysql');
var settings = {};

settings.host = "localhost";
settings.user = "root";
settings.password = "root";
settings.database = "dbname";

var pool = mysql.createPool(settings);

var countQuery = "SELECT count(*) as total FROM tbl";

var chunkSize = 1000;

pool.getConnection(function(err, connection) {
    if (err) {
        connection.release();
        console.log("Error on getConnection:", err);
        return;
    }

    connection.query(countQuery, {}, function(err, result) {
        if (err) {
            connection.release();
            console.log("Error on getConnection:", err);
            return;
        }

        if (result && result[0]) {
            var totalRows = result[0]['total'];
            console.log("Total rows in db:", totalRows);
            var periods = Math.ceil(totalRows/chunkSize)
            console.log("Total periods", periods);

            var selectQuery = "SELECT * FROM tbl ORDER BY id DESC LIMIT ";

            for(var i = 0; i < periods; i++) {
                var offset = i*chunkSize;

                var runQuery = selectQuery + offset + "," + chunkSize;

                console.log(runQuery);

                connection.query(runQuery, {}, function (err, results) {
                    if (err) {
                        console.log("Error on runQuery:", err);
                        return;
                    }

                    console.log("Data:", results);
                });
            }

            connection.release();
        }
    });
});

Upvotes: 3

Jerome Miranda
Jerome Miranda

Reputation: 1

What comes first in my mind is the dynamic pagination. I'm sure your familiar with offset and limits with mysql, with that, you can control your query.

  1. First query, get 1000 rows.
  2. If successful, add query again next 1000 rows.
  3. Do it recursively.

Upvotes: 0

Related Questions