How do I implement SQL_CALC_FOUND_ROWS on a query with UNION?

Question

Mysql, I am using SQL_CALC_FOUND_ROWS to get the total number of possible records.
How do I use it on UNION?
The only thing that works for me right now, which seems somewhat off is:

SELECT COUNT(*)
FROM(SELECT * FROM t1 UNION SELECT * FROM t2) A;

or

SELECT SQL_CALC_FOUND_ROWS *
FROM(SELECT * FROM t1 UNION SELECT * FROM t2) A;

Answer 1

From the FOUND_ROWS() documentation:

The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION statements than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole.

The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:

• The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.

• The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.

• If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.

Answer 2

You must specify SQL_CALC_FOUND_ROWS on the first SELECT in the UNION only; you don't actually need an outer SELECT query as you do when using COUNT(*).

By way of example, let's say we have the following LIMITed query:

SELECT * FROM my_table1 
UNION ALL 
SELECT * FROM my_table2 
UNION ALL 
SELECT * FROM my_table3
LIMIT 0,10;

We can simply write:

SELECT SQL_CALC_FOUND_ROWS * FROM my_table1 
UNION ALL 
SELECT * FROM my_table2 
UNION ALL 
SELECT * FROM my_table3
LIMIT 0,10;

We then call:

SELECT FOUND_ROWS();

This avoids some overhead of having the outer query mentioned in your question and in the comments of Joe Stefanelli's answer (although I'm not entirely convinced it would be a noticeable difference).

I think it's worth re-iterating that this will only work if you are using UNION ALL rather than UNION - This is because the row count is calculated before duplicates are removed, meaning you'll get the same result from FOUND_ROWS() as you would if you had used UNION ALL.