Mysql – Fastest MySQL pagination and ORDER from a huge UNION ALL VIEW

MySQLunion

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()

Best Answer

  • Rethink the need for getting the total count; it is probably the most costly part.

  • Time each of the two queries separately. Optimize them separately. Then re-time.

  • Do you really need all (*) columns? TEXT and BLOB columns extract an extra toll.

  • There is no advantage in having a VIEW; instead there is a big disadvantage in that the UNION will be run twice! (In MySQL 8.0, WITH might avoid this 'twice'.)

  • For the query with the LIMIT, you can and should do the limit on each subquery. More details here .

  • Each table needs INDEX(column1) .

This should significantly speed up (more than 2X) the big count:

SELECT  ( SELECT COUNT(*) FROM A.month )
      + ( SELECT COUNT(*) FROM A.year )
      + ( SELECT COUNT(*) FROM B.month )
      ... ;

This is because COUNT(*) is faster than either the gathering of all the rows into a temp table or the counting of rows in that table.

I predict that the result of all my suggestions will get it under 5 seconds. If not, please provide SHOW CREATE TABLE for each table. And what MySQL version you are using.

Paginate through 10K rows??

Let approach your problem from the other side. If I calculate correctly, there are about 10,000 pages in your output? What user can page through that many pages before dying if old age??

If you have more than a few dozen pages, then, for the sake of the users sanity, come up with another way to show the data to them. Pagination is not practical.

Let me provide a simple example -- Paginating through all a list of all cities in the world...

Plan A, like pagination... First have the user pick what country. (Even that is clumsy since there are about 250 countries.) Then pick a province/state/canton/etc. Then pick A..Z, then page through the cities.

Plan B, moving farther afield... Provide some "search" mechanism(s) -- for searching by part of the name, by country/state/province/etc, by population range, by part of the world, etc.

Related Question