3

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

  • Update: using SQL_CALC_FOUND_ROWS, avoid to use the select COUNT(*) and reduce time to half, but I wasn't able to use inside the view, just in a SELCT * FOM(0 – Uriel Jul 16 '18 at 19:42
  • 1
    I dispute whether both variants too the same length of time. – Rick James Jul 16 '18 at 21:53
0
  • 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.

  • Yes, the count(*) is costly, but it's needed with the current pagination plugin. I do not have TEXT and BLOB columns though. I finally used your solution without a view and reduced time around 5 seconds. – Uriel Jul 17 '18 at 16:06
  • @Uriel - Well, that pagination plugin is apparently meant for toy implementations, not large scale pagination. More . – Rick James Jul 17 '18 at 17:45
  • @Uriel - and I augmented my answer with alternatives to the simple pagination. – Rick James Jul 17 '18 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.