Speed slow down on a UNION ALL view

Question

This is my DB architecture:

• a 3.000.000 records product (memory) table that must be shared by all databases
• a x (say 1) records product table specific for each single database
• a view in each database that must UNION the two (in order to show common and specific products)

These are my tests:

• When I create the view only on memory table

  CREATE VIEW all_products AS  
      SELECT f1, f2... FROM shareddb.products
  

  the query on that view

  SELECT * FROM all_products LIMIT 1000
  

  is very fast (0.9 sec)

• When I create the UNION view (this is my target)

  CREATE VIEW all_products AS 
      SELECT f1, f2... FROM shareddb.products
      UNION ALL 
      SELECT f1, f2... FROM products
  

  the speed of the same query decreases drastically (39 sec)

How does MYSQL work in this situation and how must I set the view in order to speed it?

I use 5.7.14 version.

The query EXPLAIN returns:

id" :1 "select_type":PRIMARY "table" NULL "partitions" NULL "type" NULL "possible_keys" NULL "key" "key_len" NULL "ref" NULL "rows" NULL "filtered" NULL "Extra", matching row in const table" || "id" :2 "select_type":UNION "table" llx_product_local "partitions" NULL "type" ALL "possible_keys" NULL "key" "key_len" NULL "ref" NULL "rows" 100 "filtered" 199 "Extra" NULL

Answer 1

How does MySQL work in this situation

Generally, MySQL materializes the full result of the UNION in a temporary table. This explains why your query is so much slower than expected.

Improvements added in MySQL 5.7.3 allow some simple unions to skip materialization. This includes the case where a union appears as a top-level statement with a LIMIT clause. Sadly, it does not apply when encapsulated in a view.

db<>fiddle online demo

and how must I set the view in order to speed it?

There's nothing you can do to avoid the problem completely, even in MySQL 8. The optimizer just isn't equipped to deal with this situation.

You're left with hard-coding limits on each part of the union inside the view, perhaps with different views for different common limits, or using one of the hacks to simulate a parameterized view.