Bug #8202 | left join optimizer | ||
---|---|---|---|
Submitted: | 30 Jan 2005 13:34 | Modified: | 14 Jan 2020 21:50 |
Reporter: | Martin Friebe (Gold Quality Contributor) (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
Version: | 4.1.9, 5.x | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | qc |
[30 Jan 2005 13:34]
Martin Friebe
[30 Jan 2005 23:19]
Martin Friebe
just seen my third example is mystyped select count(*) from large1 l1 left join large2 l2 on 1=2 # impossible condition in on it takes forever, it checks every row against every in the other table
[27 May 2006 11:59]
Valeriy Kravchuk
Thank you for a resonable feature request. This kind of optimization is not performed even in latest 5.0.23-BK: mysql> explain select count(*) from large1 left join large2 on 1=2; +----+-------------+--------+-------+---------------+---------+---------+------+ ------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+ ------+-------------+ | 1 | SIMPLE | large1 | index | NULL | PRIMARY | 4 | NULL | 384 | Using index | | 1 | SIMPLE | large2 | ALL | NULL | NULL | NULL | NULL | 384 | | +----+-------------+--------+-------+---------------+---------+---------+------+ ------+-------------+ 2 rows in set (0.00 sec)
[18 Aug 2007 2:00]
Igor Babaev
- This is a request for an optimization that can be applied in rare cases. - An implementation of this optimization will require ~ 2-3 man-weeks. By the above reasons I move the bug to 'To be fixed later'. Product management will decide in what version a fix for this problem appears.
[14 Jan 2020 21:50]
Jon Stephens
Fixed in MySQL 8.0.20 together with BUG#97552, see same for docs info. Closed.