Optimizing MySQL queries from command line

Question

I want to optimize some MySQL queries.

I'm using knex in my app to construct queries. Using toSQL(), I'm able to get SQL in a format like this:

{
   sql: 'SELECT * FROM Table WHERE id = ?',
   bindings: [1]
}

My question is what's the best way to go about optimizing these queries? If I copy the sql into the command line, for use with EXPLAIN, I need a way to bind the parameters. How can I achieve this?

Or alternatively, are there any better tools than using the MySQL shell to examine the optimizer's query plan?

EDIT: I just ended up copying the query into a text editor and supplying all the parameter bindings manually.

With `EXPLAIN` you need to stub in your own values, or you need to run it in your app using some kind of query altering code, like `'EXPLAIN ' + query`. — tadman, Feb 24 '21 at 10:47
Assuming id is the PK, there's not much scope to optimise the above, other than naming the columns you actually want returned. — Strawberry, Feb 24 '21 at 13:44
Yeah, this is not the actual query i'm looking to optimize. Real queries have a bunch of joins and subqueries, ordering functions, etc. This is just to illustrate the problem simply. — CaptainStiggz, Feb 24 '21 at 21:20
Your MySQL query is what matters if you want to optimize it, not the JS you're using to send the query. That makes it unclear. Here's an answer I have to a similar question, asking about whether [JOIN vs WHERE is better](https://stackoverflow.com/a/36144454/2430549), as an example. Hope this helps. Cheers. — HoldOffHunger, Feb 24 '21 at 23:59
Yes, I can confirm that you need to supply real values to use EXPLAIN. It's up to you to choose values that are representative of your app's workload. — Bill Karwin, Feb 25 '21 at 01:25

score 1 · Accepted Answer · answered Feb 25 '21 at 13:46

I've wrote a small js method which does the bindings

function prepareStatment(obj) {
  let i = 0;
  const sql = obj.sql.replace(/\?/g, () => JSON.stringify(obj.bindings[i++]));
  copy(sql); // works only if you run it in chromes console.
  return sql;
}

prepareStatment({
   sql: 'SELECT * FROM Table WHERE id = ?',
   bindings: [1]
}) // will return `SELECT * FROM Table WHERE id = "1"`

Pay attention - this method is not safe to use in production environment

Optimizing MySQL queries from command line

1 Answers1