| Bug #1974 | index_merge method never used for non-1st tables in some types of join queries | ||
|---|---|---|---|
| Submitted: | 27 Nov 2003 12:21 | Modified: | 27 Nov 2003 13:46 |
| Reporter: | Sergey Petrunya | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[27 Nov 2003 13:46]
Sergey Petrunya
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Description: index_merge method is never used for non-first tables in join queries if previous tables are not being read using 'ref' access method. How to repeat: Query explain select * from t0,t1 where t0.key2 = 1 and (t1.key1 < t0.key1 or t1.key8 < t0.key1); will produce: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t0 type: ref possible_keys: i1,i2 key: i2 key_len: 4 ref: const rows: 1 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: i1,i8 key: NULL key_len: NULL ref: NULL rows: 1024 Extra: Range checked for each record (index map: 0x81) while query explain select * from t0,t1 where t0.key2 < 2 and (t1.key1 < t0.key1 or t1.key8 < t0.key1); will produce: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t0 type: range possible_keys: i1,i2 key: i2 key_len: 4 ref: NULL rows: 3 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: i1 key: NULL key_len: NULL ref: NULL rows: 1024 Extra: Range checked for each record (index map: 0x01) 2 rows in set (0.00 sec) i.e. possible_keys and index map are generated incorrectly.