MySQL Bugs: #71334: Small join on composite PK not performing well versus IN-list

Bug #71334	Small join on composite PK not performing well versus IN-list
Submitted:	9 Jan 2014 20:26	Modified:	24 Feb 2014 22:26
Reporter:	Greg Kemnitz	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6.15-enterprise-commercial-advanced-lo	OS:	Solaris
Assigned to:		CPU Architecture:	Any

Description:
If I have a table like

create table foo (int a, float b, int c, d, e, primary key (a, b, c)) engine=innodb partition by hash(c) partitions 300;

and I create a very small table with only three elements:

create temporary table bar (int a) engine=memory;

and I do the following

select f.* from foo f, bar b where b.a = f.a and f.b <= -5.7 and f.c between 1234 and 2345;

...I get performance that is a whole lot slower than this query:

select f.* from foo f where f.a in (1, 2, 3) and b.a = f.a and f.b <= -5.7 and f.c between 1234 and 2345;

In the actual table, performance is about 30 ms for the IN form and about 3 seconds for the join form if everything's in the buffer pool, even though the join table only has the same three recs.  The actual "foo" table has about 500M records in it, with about 2.5M recs per partition.  I originally thought partition pruning wasn't working correctly, but performance appears consistent with the composite PK direct search inside each partition only being used for the join key and not the literal used in the "b" part.  (Note that I tried to put "5.7" in the "t" table and performance was the same.)

How to repeat:
See above.  Create a decently big "foo" table with similar structure to above.

Suggested fix:
Fix the join so the lookup on the PK uses both parts of the key, not just the leading key.

The IN form is actually this:

select f.* from foo f where f.a in (1, 2, 3) and f.b <= -5.7 and f.c between 1234 and 2345;

Thank you for the report.

I can not repeat described behavior. Please provide configuration options you use.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".