'Optimizing a view including a union in Mysql before version 8.0

I have a view that combines the contents of two tables like this

CREATE VIEW my_view AS
(SELECT some_key, x FROM a)
UNION ALL
(SELECT some_key, x FROM b)

I want to query the view like this:

SELECT * FROM my_view WHERE some_key = '123'

This is possible but it's very slow since the complete view seems to be materialized before applying the filter/condition. In theory the condition can be pushed down to both the selects the union is made of but it's not happening. It appears this feature is available in MySQL 8.0

How can I work around this in earlier versions of MySQL? I thought about left joining both tables to another table with the same key to trick MySQL into applying the where condition first but I didn't succeed. I assume it would even be faster when I would run over the matching keys and generate the union separately only for the matching records in both tables. I thought about doing this with a kind of subquery within a select on the third table (containing only the keys) but i don't know how I could union the results again.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source