MySQL Bugs: #48767: IBMDB2I subselect performance degrades when async buffering enabled

Bug #48767	IBMDB2I subselect performance degrades when async buffering enabled
Submitted:	13 Nov 2009 20:17	Modified:	1 Jan 2011 15:49
Reporter:	Tim Clark	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: DB2SE for IBM i	Severity:	S5 (Performance)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Execution of some subselect queries over indexed fields has poorer performance when run with IBMDB2I asynchronous buffering enabled. This is contrary to the intent and design of asynchronous buffering. Performance should never degrade when asynchronous buffering is enabled.

How to repeat:
Run the following query twice against the attached data, once with ibmdb2i_async_enabled set to 0 and once set to 1:

select count(*) from table_ibmdb2i, (select b, max(c) as max_c from table_ibmdb2i group by b) as max_status where table_ibmdb2i.b = max_status.b and table_ibmdb2i.c = max_status.max_c;

The query execution time with ibmdb2i_async_enabled=0 is about half the time that is required when ibmdb2i_async_enabled=1.

Suggested fix:
Improve the heuristic that the storage engine uses to determine when to use asynchronous buffering so that it is not necessary to manually alter the value of ibmdb2i_async_enabled to accomodate each query.

This storage engine is no longer supported. See http://dev.mysql.com/doc/refman/5.1/en/news-5-1-54.html.