I'm trying to run a code that optimizes tables in the database. I want to run this query in loop as each table take 3-5 mins to execute so once the execution of one table is done the other table should execute. Any suggestions regarding it.

const test=  'OPTIMIZE TABLE TestT1';
connection.query(test, function (error, results, fields) {
    
    if (error) {
        console.log("Error: "   error);
        callback(error, "Error: "   error);
    } else {
        console.log("Query success");
        console.log("results:",results);
        callback(error, results.length > 0 ? results : null);
    }
});
};

CodePudding user response：

Write a function that optimizes a single table and returns a promise you can wait for. Put all the tables into an array, loop over the array, awaiting the function.

function optimize_table(connection, tablename) {
  return new Promise((resolve, reject) =>
    connection.query(`OPTIMIZE TABLE ${tablename}`, (error) => {
      if (error) {
        console.log(error);
        reject(error);
      } else {
        resolve();
      }
    })
  });
}

const tables = ["table1", "table2"];
for (let i = 0; i < tables.length; i  ) {
  await optimize_table(connection, tables[i]));
}

For more ideas, see How to interact with MySQL database using async/await promises in node.js

CodePudding user response：

OPTIMIZE TABLE is essentially useless, especially for ENGINE=InnoDB. I recommend you abandon the process rather than trying to speed it up.

One of the rare cases where OPTIMIZE can be useful is just after deleting lots of rows from a table. But there are better ways to do the delete, so that is another case to simply avoid.