MySQL Union – Combining Explain and Union for Better Performance

explainMySQLunion

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:

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.

Best Answer

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.

Related Solutions

Mysql – How to combine UNION and INNER JOIN

Just do a LEFT JOIN instead:

SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P             
LEFT JOIN product_groups GP ON P.parent = GP.id

That will return all rows from P and any non-matching rows in GP will have NULL values.

Combining 2 queries

It sounds like you goal can be met using a UNION. UNION combines two select statements into a single resultset. The order by is applied to the unioned query not the individual select statements. You'll need to order the results after the union is applied.

See: http://technet.microsoft.com/en-us/library/ms180026.aspx

Here is an example using your queries.

Select
      c.Ship_Name,
      a.Voy, 
      a.Start_Pos AS [Start],
      a.End_Pos AS [End],
      '' AS Activity,
      a.Comments_Pos AS Comments
From Table1 a, Table2 c
Where a.Ship_Code = c.Ship_Code
      And a.Port_Type = 'W'

UNION

SELECT 
  c.Ship_Name,
  a.Voy,
  b.Start_Act AS [Start],
  b.End_Act AS [End],
  b.Activity AS Activity,
  b.Comments_Act AS Comments
From Table1 a, Table2 c, Table3 b
Where a.Ship_Code = c.Ship_Code
  And a.Key = b.Key 
  And b.Offh = 'W'

Best Answer

Related Solutions

Mysql – How to combine UNION and INNER JOIN

Combining 2 queries

Related Question