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