MySQL Bugs: #17314: Index_merge/intersection is not choosen for MERGE tables

Bug #17314	Index_merge/intersection is not choosen for MERGE tables
Submitted:	10 Feb 2006 23:47	Modified:	27 Feb 2006 19:47
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0-bk	OS:	Any (any)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
Index_merge/intersection is not choosen for MERGE table, while optimizer choices for underlying MyISAM tables make one expect Index_merge/intersection to be used for the MERGE table too.

How to repeat:
# Create the tables.
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
  a int, b int, 
  filler1 char(200), filler2 char(200),
  key(a),key(b)
);
insert into t1 select @v:= A.a, @v, 'filler', 'more filler' from ten A, ten B, ten C;

create table t2 like t1;
create table tm (
  a int, b int, 
  filler1 char(200), filler2 char(200),
  key(a),key(b)
) engine=merge union=(t1,t2);

# now run EXPLAINs:
# for MyISAM table, Index_merge/intersection is used:
explain select * from t1 where a=1 and b=1;
# for Merge of the above table with an empty table, Index_merge/intersection 
# is not used:
explain select * from tm where a=1 and b=1;

Suggested fix:
This happens because ha_myisammrg::block_size == 0, and that makes index cost  calculations to produce bogus results.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2473

Because of merge jams and tree breaks. Will do another push attempt now...

The fix has been pushed into 5.0.19 tree.
Note for the changelog:
The bug was that internal (not user-visible) parameters were set incorrectly for MERGE tables, and because of that cost-based range/index_merge optimizer assumed that using index_merge/intersection was too expensive.

Documented in 5.0.19 changelog:

    <listitem>
        <para>
          For certain <literal>MERGE</literal> tables, the optimizer
          wrongly  assumed
          that using <literal>index_merge/intersection</literal> was too
          expensive. (Bug #17314)
        </para>
      </listitem>