0

I have two ways to select MAX values from two tables but don't know which one is better or faster. i checked from mysql workbench but in case both query i'm getting 0.00 Sec execution time

First query

SELECT MAX(s) from (
  SELECT sheetid  s FROM csheets
  UNION all
  SELECT sheetid s FROM queueitems
) as t

Explain enter image description here

Second Query

SELECT MAX(s) from (
  SELECT MAX(sheetid)  s FROM csheets
  UNION all
  SELECT MAX(sheetid) s FROM queueitems
) as t

Explain enter image description here So question is which one better in terms of speed?

Muhammad Haseeb Khan
  • 885
  • 9
  • 24
  • I guess instead of using `MAX` you can use `order by sheetid desc limit 1` – M Khalid Junaid Mar 02 '14 at 10:05
  • First create indexes on `sheetid` column for both tables--> `CREATE INDEX indexname1 ON csheets( sheetid )` and `CREATE INDEX indexname2 ON queueitems( sheetid )`, this is a must, because at present both queries perform full table scans. Then run explains again and post results. – krokodilko Mar 02 '14 at 10:40

1 Answers1

1

The second one would be better, because the aggregation is performed before, you will have one step less on the temporary table than with the first query.

However take a look at the execution plan, with the EXPLAIN .

aleroot
  • 71,077
  • 30
  • 176
  • 213