MySQL Bugs: #41873: MySQL doesn't use indexes when joining large table to small one

Bug #41873	MySQL doesn't use indexes when joining large table to small one
Submitted:	5 Jan 2009 15:52	Modified:	12 Jan 2009 21:12
Reporter:	Johan Strom	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.30-community, 5.0, 5.1, 6.0 bzr	OS:	Any (MS Windows, Linux)
Assigned to:		CPU Architecture:	Any

Description:
When performing a long but simple series of joins, I noticed that some queries took 20+ seconds to execute (and occasionally crashing mysqld) instead of the expected 0.05 seconds, I poked around and discovered that it occurred on a join with a table that at that point only had one row, which somehow led to a table scan of another table in the join that had 120k rows. I also discovered that adding exactly 6 rows into the small table caused the problem to go away. Here is a screenshot of an explain of the two different outcomes:

http://img511.imageshack.us/img511/9986/joinssmalltl2.png

The ONLY difference between these two are the amount of rows in table VFaction.

Some conversation in #mysql prompted me to fill a bug report, as I don't think I'm doing anything wrong anymore.

Some people suggested altering the db design to not include a table with only one row in it, but that's not an option - there are many legitimate situations where this table contains many rows.

How to repeat:
1. Load up this file containing a test database:

http://82.182.97.10/magical_index_mystery_inside.zip

2. Run the query that can be found in a text file in there on it and note that mysql is performing file sorts and all manner of things that slow down a lot when I have millions of rows.

3. Add a number (for this file, on my system, it's 5) of rows into "faction" table.

4. Run the query again - now joins without oddities and with exceptional speed no matter the row counts.

Suggested fix:
Make it work "properly" :).

Please!

Test DB + query

Attachment: magical_index_mystery_inside.zip (, text), 354.96 KiB.

I didn't realize it was possible to attach files, so I now attached the file with the test database.

Thank you for the report.

Verified as described.

test case

Attachment: bug41873.test.tar.gz (application/x-gzip, text), 262.86 KiB.

shorter test case for 5.1 and 6.0

Attachment: bug41873.test (application/octet-stream, text), 110.38 KiB.

If this bug indeed leads to crash from time to time, it may deserve higher severity and impact.