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

  • What version of MySQL? Edit the question and add the respective tag. – ypercubeᵀᴹ Jul 26 '17 at 10:16
  • 1
    Also: try defining the view with algorithm = merge. – ypercubeᵀᴹ Jul 26 '17 at 10:17
  • No, I just checked. Even in 5.7, UNION and UNION ALL - among other things - forbid the use of merge algorithm. – ypercubeᵀᴹ Jul 26 '17 at 11:54
  • VIEW is just an alias for underlying SELECT. Try to make the UNION via direct SELECTs and full qualificators like shareddb.products and inspect it via EXPLAIN – Kondybas Jul 26 '17 at 11:55
  • As ypercube says, with my actual version (5.7.14) the merge algoritm is not possible with UNION clause, The error is: "View merge algorithm can't be used here for now (assumed undefined algorithm)". – Zeno Jul 26 '17 at 12:10

Your Answer

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

Browse other questions tagged or ask your own question.