4

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

0

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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