Question 288947 in Mysql Question posted in
The official MySQL documentation can be found here.

How to query all tables data available in MySQL database using node.js

I am new to the node.js script I want to calculate the sum of Distance column where the Distance column is available in every table in database before I used looping through tables but it takes so much time to give every table result, is there any method to fetch result fast.
this is the sample code which I am using for individual table selection now I want for all tables

app.post('/fetch-data', (req, res) => {
   const selectedTable = req.body.table;
   const dateTimeRange = req.body.dateTimeRange;
   const [fromDateTime, toDateTime] = dateTimeRange.split(' - ');
   const fromDateTimeObj = new Date(fromDateTime);
   const toDateTimeObj = new Date(toDateTime);
   const fromDateTimeSQL = fromDateTimeObj.toISOString().slice(0, 19).replace('T', ' ');
   const toDateTimeSQL = toDateTimeObj.toISOString().slice(0, 19).replace('T', ' ');  // querying database and fetching data based on selected table and selected date
   const fetchDataQuery = `SELECT * FROM `${selectedTable}`WHERE date_time BETWEEN STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s');`;
   const getTableNamesQuery = "SELECT TABLE_NAME FROM information_schema.TABLES WHERE table_schema = 'ingodata';";
   pool.query(getTableNamesQuery, (err, results) => {
       if (err) {
           console.error('Error fetching table names:', err);
           return res.status(500).json({ error: 'Error fetching table names' });
       }
       const tableNames = results.map((row) => row.TABLE_NAME);

   });
   pool.query(fetchDataQuery, [fromDateTimeObj, toDateTimeObj], (err, results) => {
       if (err) {
           console.error(`Error fetching data from ${selectedTable}:`, err);
           return res.status(500).send(`Error fetching data from ${selectedTable}`);
       }
       if (!results || results.length === 0) {
           // Handle the case when no data is found
           const noDataHtml = `
           <h1>No data found for the selected date range.</h1>
           `;
           return res.send(noDataHtml);
       }
const data = results;
Tags:

2

Answers


  1. 0

    There are few things you can do to calculate sums of distance and query it for every table.

    1. Instead of using for loop to loop over table names and use
      Promise.all(Read more here). It will call all the promises
      nearly together which will drop total query time. While using for
      loop it’s sequential, meaning 1 async request will wait for another.
      But for that you will have to promisify callback functions since
      Promise.all accepts array of promises. You can read more on how to
      promisify callback function here. pros -> It is relatively
      small change for your current code and if you have small datasets,
      it may be right solution for you. cons-> Does not scale well,
      if your dataset is big, querying some of distance maybe even issue
      in 1 table, let alone on every table and in nearly same time. Can
      potentially cause slow queries and even timeouts and db overloading.
    2. Create new table where you will save total distance for each of the table. So you would have columns like total_distance_table_a, total_distance_table_b and etc… If distance updates in one of the tables, you would also update it in real time here. This way you will be making only 1 query to get all your totals. This will simplify your code, dramatically reduce load on database and greatly increase average query time.

    I would recommend 2nd approach, since it simplifies your code and scales much better than your looping over tables and querying total distance from them

    Login or Signup to reply.
  2. 0

    Looking at your code there are a number of areas for improvement:

    1. Using SELECT * reads and returns all columns. See if you can select only the columns you need, or aggregate them at the db already, e.g. Select SUM(distance) ...

    2. Interating over ALL tables sounds like a bad idea. DBs are optimized to iterate over rows. If you really need to split your data over several tables, Partitioning may be useful to logically split data. See answers here on how to partition a table by a datetme column

    3. You can explore using a cache table (or stored view) to speed up your queries. The cache is optimized for quick lookups and is updated whenever base tables are updated. Window functions may come useful. However, I would explore caching only when #1-2 are fixed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search