MySQL Tips and Tricks

(Also NodeJS)

Execute unrelated SQL queries in parallel

Any SQL queries that do not directly depend on one another can be grouped by q.all() and then .spread(). This has the following advantages:

1. Faster! MySQL can process all the queries as soon as possible in parallel.
2. More compact - less code.
3. More expressive - less .then().then()

const queryTable = `
  SELECT debit, credit FROM table ORDER BY table.date WHERE table.account = ?;
`;

const queryAggregates = `
  SELECT SUM(debit) AS debits, SUM(credit) AS credits
  FROM table ORDER BY table.date WHERE table.account = ?;
`;

// executes all sql
function executeSqlQueries(id) {
  return q.all([
    db.exec(queryTable, [id]),
    db.exec(queryAggregates, [id]),
  ]);
}

executeSqlQueries(12)
  .spread((rows, aggregates) => {
    return { rows, aggregate }; // do something awesome
  });