Bug #19375 | ref-Index scans loops through NULL records even though later discarded | ||
---|---|---|---|
Submitted: | 26 Apr 2006 15:07 | Modified: | 18 Sep 2006 7:52 |
Reporter: | Jochen Riehm | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.0.23-BK, 5.0.19 | OS: | Linux (Linux) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[26 Apr 2006 15:07]
Jochen Riehm
[26 Apr 2006 15:08]
Jochen Riehm
FYI: the value of myisam_stats_method has no effect on described behaviour. Just EXPLAIN looks a little different.
[26 Apr 2006 15:16]
Jochen Riehm
To verify that this is actually a problem with NULL-values do the following: ALTER TABLE `test1` CHANGE `auto_id` `auto_id` INT( 11 ) NOT NULL DEFAULT '0' and run statements again. Result: +---------------------+ | VERSION() | +---------------------+ | 5.0.19-standard-log | +---------------------+ 1 row in set (0.00 sec) +------------------------+---------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +------------------------+---------+----------+-----------------------------+ | dbcarsharing_tmp.test1 | analyze | status | Table is already up to date | | dbcarsharing_tmp.test2 | analyze | status | Table is already up to date | | dbcarsharing_tmp.test3 | analyze | status | Table is already up to date | +------------------------+---------+----------+-----------------------------+ 3 rows in set (0.00 sec) +----+-------------+-------+-------+--------------------------------+-------------+---------+-----------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+--------------------------------+-------------+---------+-----------------------------+------+-------------+ | 1 | SIMPLE | a | range | altautofind,firma_id | altautofind | 4 | NULL | 1440 | Using where | | 1 | SIMPLE | ap | ref | auto_id,doppelt_aber_gebraucht | auto_id | 4 | dbcarsharing_tmp.a.id | 2 | | | 1 | SIMPLE | abp | ref | auto_id | auto_id | 4 | dbcarsharing_tmp.ap.auto_id | 3 | | +----+-------------+-------+-------+--------------------------------+-------------+---------+-----------------------------+------+-------------+ 3 rows in set (0.00 sec) +----------+ | COUNT(*) | +----------+ | 793 | +----------+ 1 row in set (0.03 sec)
[11 Jun 2006 14:36]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described, with your test data and statements, on 5.0.23-BK. Query worked for 11+ seconds in my case. In slow log I've got: # Time: 060611 13:09:00 # User@Host: root[root] @ localhost [] # Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 2594467 SET timestamp=1150020540; SELECT COUNT(*) FROM test2 a LEFT JOIN test3 ap ON ( a.id = ap.auto_id AND ( ap.bis > NOW() OR ap.bis IS NULL ) ) LEFT JOIN test1 abp ON (ap.auto_id = abp.auto_id AND (ap.von < abp.bis OR ISNULL(abp.bis) ) AND (ISNULL(ap.bis) OR ap.bis > abp.von)) WHERE a.verkaufdat IS NOT NULL AND LENGTH(a.verkaufdat) > 0 AND a.firma_id IN (3,3000); Note rows examined! Same results for both myisam_stats_method=nulls_equal and myisam_stats_method=nulls_unequal.
[14 Sep 2006 18:16]
Sergey Petrunya
This problem was fixed by fix for BUG#21390, in the same way BUG#19649 (see BIG#1649, comment dated 14 Sep 19:39)
[18 Sep 2006 7:52]
Jochen Riehm
As I do not have access to duplicate bug 21390, could you please copy in here the MySQL version in which this bug has been fixed?
[18 Sep 2006 9:12]
Valeriy Kravchuk
Bug #21390 is fixed in 5.0.25 and 5.1.12.