0

Morning all, I'm trying to quickly compare multiple queries that do similar tasks and I'm wondering if there's a quicker way of lining up the EXPLAIN outputs. To me, this example query should work:

EXPLAIN SELECT count(*) AS thetotal, date(datetimefield) AS theday, key_id FROM dbname.tablename WHERE datetimefield >= "2015-12-12 00:00:00" AND datetimefield <= "2015-12-12 23:59:59" GROUP BY key_id UNION
EXPLAIN SELECT count(*) AS thetotal, date(datetimefield) AS theday, key_id FROM dbname.tablename WHERE DATE(datetimefield) = "2015-12-12" GROUP BY key_id, theday;

and output something like the following:

Two rows of explain select output

but as you probably know it gives a "Syntax error, unexpected describe" message. Is there any reason why it shouldn't, or a way to achieve a similar result? At the moment I'm running EXPLAIN on the two similar queries, exporting them to csv, opening both in a spreadsheet editor and pasting them one row after the other.

Thanks in advance for any assistance you can give me.

  • Is the command line an option? If so, you could do basically what you described quickly with mysql -u -p -Bse"EXPLAIN" >> file.txt twice. – WAF Dec 15 '15 at 13:15
  • UNION can only combine two SELECTs, EXPLAIN is not a SELECT even when it seems to just return rows. – jkavalik Dec 15 '15 at 13:19
  • Thanks @WAF, command line definitely an option. If you want to make that an answer I'll choose it. – Samuel Barnett Dec 15 '15 at 15:14
  • The first SELECT is much better than the second if you have INDEX(datetimefield). This is because the second hides the index column inside a function (DATE). – Rick James Dec 16 '15 at 23:52
2

As @jkavalik said in a comment, according to the MySQL docs UNION can only be used to combine the outputs of SELECTs, which EXPLAIN is not.

However, you can line up the raw outputs of each EXPLAIN in a text file using the MySQL commands in a shell. Run this once for each EXPLAIN statement:

mysql -u $USERNAME -p$PASSWORD -Bse "[EXPLAIN STATEMENT GOES HERE]" >> explains.csv

From the MySQL docs:

-B: Print results using tab as the column separator

-s: Silent mode. Produce less output.

-e statement: Execute the statement and quit.

This will give you a tab delimited row and >> will append to the file specified at the end, creating it if it is not already there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.