Let's say we have a schema with table 'month' which have the current information and table 'year' which have the historic information. Each table has around 65,000 rows.
This schema exists in several places, let's say place A, B, and C.
It's requested to fetch ordered information from every schema. No possible 'materialized view'
According to me, I need to unite all the results (without LIMIT):
CREATE VIEW all_results AS SELECT * FROM A.month UNION ALL SELECT * FROM A.year UNION ALL SELECT * FROM B.month ....
...and then apply the ORDER BY, the COUNT and the LIMIT
SELECT * FROM all_results ORDER BY column1 LIMIT 0,23 SELECT count(*) FROM all_results
but this approach is taking almost 20 seconds (which is too much for a web solution).
Is there a better way to do this?
(Added from dup question) This variant also takes 20 seconds.
SELECT SQL_CALC_FOUND_ROWS * FROM( SELECT * FROM A.month UNION ALL SELECT * FROM A.year UNION ALL SELECT * FROM B.month .... ) AS united LIMIT 0,23
And the count the total: SELECT FOUND_ROWS()