Optimizer Decisions change with Storage Engine [MyISAM, InnoDB]

[Drei Bit]

Hello MySQL-Optimizers,
why does the Optimizer changes the way to access the data when the Storage Engine changes?
In this Example (derived from a situation i have) I will construct a Case where the Decision made should not be dependent on the Storage Engine.


the Tables as follows:

CREATE TABLE A (
   ID INT NOT NULL AUTO_INCREMENT,
   dummy TEXT NOT NULL,

   PRIMARY KEY (id)
) ENGINE = InnoDB;

CREATE TABLE B ( 
	IDX INT NOT NULL AUTO_INCREMENT,
	ID INT NOT NULL,
	dummy VARCHAR(200),
	INDEX b_ix_dummy USING BTREE(dummy),
	INDEX b_ix_dummy_id USING BTREE(dummy, id),

	CONSTRAINT pk_b PRIMARY KEY (IDX)
) ENGINE = MyISAM;

CREATE TABLE C ( 
	IDX INT NOT NULL AUTO_INCREMENT,
	ID INT NOT NULL,
	dummy VARCHAR(200),
	INDEX c_ix_dummy USING BTREE(dummy),
	INDEX c_ix_dummy_id USING BTREE(dummy, id),

	CONSTRAINT pk_c PRIMARY KEY (IDX)
) ENGINE = InnoDB;

--query1
select * 
from A 
	join (select id from b where dummy like 'xyz%') x on x.id = a.id


-- query2
select * 
from A 
	join (select id from c where dummy like 'xyz%') x on x.id = a.id

Then Explain is run (with lots of testdata):

for query1:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	<null>	<null>	<null>	<null>	xxx	
1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	x.id	1	
2	DERIVED	b	range	b_ix_dummy,b_ix_dummy_id	b_ix_dummy	xxx	<null>	xxx	Using where;

for query2:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	<null>	<null>	<null>	<null>	xxx	
1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	x.id	1	
2	DERIVED	c	range	c_ix_dummy,c_ix_dummy_id	c_ix_dummy_id	xxx	<null>	xxx	Using where; Using index

The Difference is clear! The first one is using the wrong index. It should use like the second one the combined Index, because this would also enable using only the index without even looking at the table data.

Why is there a difference?



Edited 3 time(s). Last edit at 07/04/2007 10:53AM by Drei Bit.