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

Question

I am trying to calculate the total number of rows that would be returned if there was no global LIMIT set. This is 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 this with a 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 read strings WITHOUT using UNION ALL?

Answer 1

Use a temporary table for all selected connections:

 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