MySQL Bugs: #24012: Optimization of UNION clauses

Bug #24012	Optimization of UNION clauses
Submitted:	6 Nov 2006 14:19	Modified:	6 Jan 2022 17:13
Reporter:	Jean-David Maillefer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.1.12, 5.0.24	OS:	Linux (debian)
Assigned to:		CPU Architecture:	Any

Description:
The WHERE clause in a UNION seems not to be optimized in obvious ways

How to repeat:

select * from (
(
SELECT `id` , `email` , `password` , `status` , 'ETUD' AS `type`
FROM `etudiants`.`logins`
)
UNION (
SELECT `id` , `email` , `password` , `status` , 'J4ST' AS `type`
FROM `j4st`.`logins`
) ) as l0
WHERE id = 23

In this case, the union is made first and then searched for the id (very slow if the tables are big). I had expected a the smartest behaviour (note that both tables are indexed on id):

select * from (
(
SELECT `id` , `email` , `password` , `status` , 'ETUD' AS `type`
FROM `etudiants`.`logins`
WHERE id = 23
)
UNION (
SELECT `id` , `email` , `password` , `status` , 'J4ST' AS `type`
FROM `j4st`.`logins`
WHERE id = 23
) ) as l0

Which is many times faster...
And this kind of query is often put in VIEW, which exhibits the same lack of optimization...

Suggested fix:
Optimize UNION clause when possible

Thank you for a reasonable feature request.

I too would very much appreciate more efficient behavior here.  Has there been any progress?  Thanks. ~Jacob

- This problem will be resolved when WL#3485 is implemented (not started yet).

By the above reason I move the bug to 'To be fixed later'.
Product management will decide in what version a fix for this problem appears.

WL#3485 should be implemented in 6.0. Do we have this optimization in 6.0.x already?

Fixed in MySQL 8.0.29 by WL#13730. See same for info.

Closed.