MySQL Bugs: #1679: Index not used in left join after 4.0.16 upgrade

Bug #1679	Index not used in left join after 4.0.16 upgrade
Submitted:	27 Oct 2003 11:43	Modified:	30 Jan 2004 1:30
Reporter:	Philip Turner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.16	OS:	Linux (Linux - Intel)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
An index which was used to optimize a left join in version 4.0.12 is now ignored in version 4.0.15 and 4.0.16

In version 4.0.12 an explain for this query yields:
*************************** 3. row ***************************
table: dvo_ro_dups
type: ref
possible_keys: PRIMARY,project_acct
key: PRIMARY
key_len: 35
ref: const,sv_rohdr.vin,sv_rohdr.ro_num
rows: 617
Extra: Using where; Using index; Distinct

In version 4.0.16 an explain now yields:
*************************** 3. row ***************************
table: dvo_ro_dups
type: range
possible_keys: PRIMARY,project_acct
key: PRIMARY
key_len: 8
ref: NULL
rows: 463
Extra: Using where; Using index; Distinct

How to repeat:
Attached is bug_rpt.tar.gz which contains:
bug_rpt_db.sql - SQL commands to load into empty database
bug_rpt_test.sql - SQL commands to run against server with the command
mysql --host=xxxx --user=yyyy --passsord --table dbname < bug_rpt_test.sql
4.0.12.rpt - Output of bug_rpt_test.sql commands against 4.0.12 database
4.0.16.rpt - Output of bug_rpt_test.sql commands against 4.0.16 database

Note - In my instance, loaded the database using 4.0.12, stopped the server, switched the software to 4.0.16, started the server, issued the command to generate the 4.0.16.rpt, stopped the server, reverted the server software back to 4.0.12, started the server, then issued the command to generate the 4.0.12.rpt

Suggested fix:
A workaround for 4.0.16 is to add "use index (primary)" to the left join dvo_ro_dups and the behavior goes back to the way it used to work in 4.0.12

This appears similar to an issue reported in bug 1724 (changes to FULL SCAN costs in 4.0.16), which has been corrected in the latest 4.0.17 BitKeeper sources.  The query now uses an index again without optimizer hints.

Thank you

Still fails after 4.0.17 upgrade using downloaded binaries.

Here's running against a 4.0.12-max server (note on the dvo_ro_dups table the index and length used):
152$ mysql --host=barney --user=root -p -E bug_rpt</tmp/bug_rpt1.sql
Enter password:
*************************** 1. row ***************************
table: xmit_sv_rohdr
type: ref
possible_keys: PRIMARY,ret_prj_xmit,prj_xmit
key: ret_prj_xmit
key_len: 12
ref: const,const
rows: 11
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
table: sv_rohdr
type: eq_ref
possible_keys: PRIMARY,ret_prj_vin_inv
key: PRIMARY
key_len: 22
ref: xmit_sv_rohdr.ret_num,xmit_sv_rohdr.data_prj_code,xmit_sv_rohdr.ro_num
rows: 1
Extra:
*************************** 3. row ***************************
table: dvo_ro_dups
type: ref
possible_keys: PRIMARY,project_acct
key: PRIMARY
key_len: 35
ref: const,sv_rohdr.vin,sv_rohdr.ro_num
rows: 617
Extra: Using where; Using index

Here's running against a 4.0.17-standard server. Now the wrong
index.
153$ mysql --host=elvis --user=root -p -E bug_rpt</tmp/bug_rpt1.sql
Enter password:
*************************** 1. row ***************************
table: xmit_sv_rohdr
type: ref
possible_keys: PRIMARY,ret_prj_xmit,prj_xmit
key: ret_prj_xmit
key_len: 12
ref: const,const
rows: 11
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
table: sv_rohdr
type: eq_ref
possible_keys: PRIMARY,ret_prj_vin_inv
key: PRIMARY
key_len: 22
ref: xmit_sv_rohdr.ret_num,xmit_sv_rohdr.data_prj_code,xmit_sv_rohdr.ro_num
rows: 1
Extra:
*************************** 3. row ***************************
table: dvo_ro_dups
type: ref
possible_keys: PRIMARY,project_acct
key: project_acct
key_len: 8
ref: const
rows: 616
Extra: Using where

Here's after changing the dvo_ro_dups.project='subaruro' to
dvo_ro_dups.project=sv_rohdr.prj_code on the 4.0.12-max server:
154$ mysql --host=barney --user=root -p -E bug_rpt</tmp/bug_rpt2.sql
Enter password:
*************************** 1. row ***************************
table: xmit_sv_rohdr
type: ref
possible_keys: PRIMARY,ret_prj_xmit,prj_xmit
key: ret_prj_xmit
key_len: 12
ref: const,const
rows: 11
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
table: sv_rohdr
type: eq_ref
possible_keys: PRIMARY,ret_prj_vin_inv
key: PRIMARY
key_len: 22
ref: xmit_sv_rohdr.ret_num,xmit_sv_rohdr.data_prj_code,xmit_sv_rohdr.ro_num
rows: 1
Extra:
*************************** 3. row ***************************
table: dvo_ro_dups
type: ref
possible_keys: PRIMARY,project_acct
key: PRIMARY
key_len: 35
ref: sv_rohdr.prj_code,sv_rohdr.vin,sv_rohdr.ro_num
rows: 2
Extra: Using index

Here's after changing the dvo_ro_dups.project='subaruro' to
dvo_ro_dups.project=sv_rohdr.prj_code on the 4.0.17-standard server:
155$ mysql --host=elvis --user=root -p -E bug_rpt</tmp/bug_rpt2.sql
Enter password:
*************************** 1. row ***************************
table: xmit_sv_rohdr
type: ref
possible_keys: PRIMARY,ret_prj_xmit,prj_xmit
key: ret_prj_xmit
key_len: 12
ref: const,const
rows: 11
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
table: sv_rohdr
type: eq_ref
possible_keys: PRIMARY,ret_prj_vin_inv
key: PRIMARY
key_len: 22
ref: xmit_sv_rohdr.ret_num,xmit_sv_rohdr.data_prj_code,xmit_sv_rohdr.ro_num
rows: 1
Extra:
*************************** 3. row ***************************
table: dvo_ro_dups
type: ref
possible_keys: PRIMARY,project_acct
key: PRIMARY
key_len: 35
ref: sv_rohdr.prj_code,sv_rohdr.vin,sv_rohdr.ro_num
rows: 2
Extra: Using index
156$

As I am seeing this in both the 4.0.17 binary and the current source tree, and as the bug I marked this as duplicating does appear to be resolved, I am going to verify this on its own.

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

Additional info:

First: it was pure chance that MySQL 4.0.12 used the correct index in this case.

When I tested this on the latest MySQL 4.0 version, MySQL choosed the index 'project_acct' which was far from perfect.

The bug was that MySQL didn't handle the case where the first used key part is a constant; In this case all keys that had this key part first where threated as equal.

I have now tuned the optimizer to handle this case. The fix will be in MySQL 4.0.18 and 4.1.2
After the fix, MySQL will use the full primary key as an index for your query in bug_rpt.tar