SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() on UNION query (not UNION ALL) with LIMIT

Question

I'm trying to count the total number of rows that would have been returned if there was no global LIMIT set. Here's what my query looks like:

SELECT SQL_CALC_FOUND_ROWS * FROM table1 WHERE [...]
UNION
SELECT * FROM table2 WHERE [...]
UNION
SELECT * FROM table3 WHERE [...]
UNION 
SELECT * FROM table4 WHERE [...]
LIMIT 0,30

Then I immediately follow that with the SELECT FOUND_ROWS() query. It returns a total of 35 rows, but it should return a total of 400 rows. Any idea how I can count the rows WITHOUT using UNION ALL?

Answer 1

Use temporary table for all union selects:

SELECT SQL_CALC_FOUND_ROWS * FROM 
(
  SELECT * FROM table1 WHERE [...]
  UNION
  SELECT * FROM table2 WHERE [...]
  UNION
  SELECT * FROM table3 WHERE [...]
  UNION 
  SELECT * FROM table4 WHERE [...]
)  temp_table
LIMIT 0,30