Hello Optimization-Forum,
I recently noticed another strange behavior of the Optimizer:
Example
CREATE TABLE t1(
id INT NOT NULL AUTO_INCREMENT,
dummy TEXT NOT NULL,
CONSTRAINT pk_t1 PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE t2(
lid INT NOT NULL AUTO_INCREMENT,
id INT NOT NULL,
sometext TEXT,
CONSTRAINT pk_t2 PRIMARY KEY (lid),
INDEX ix_id (id),
INDEX ix_sometext (sometext(250))
) ENGINE=InnoDB;
explain
select * from t1
where id in (select id from t2 where sometext like 'hall%');
With proper testdata will generate the result:
id select_type table type possible_keys key key_len ref Rows Extra
1 PRIMARY t1 ALL <null> <null> <null> <null> 1 Using where
2 DEPENDENT SUBQUERY t2 index_subquery ix_id,ix_sometext ix_id 4 func 1 Using index; Using where
First of all the Query is by no means a DEPENDENT_SUBQUERY. It should only be evaluated once (I mean it is constant! It is not correlated!). Only the columns 'id' accidentally have the same name.
Secondly the wrong index was chosen. The Optimizer should pick the ix_sometext Index and perform a range query, given the selectiveness is relative small.
And Thirdly the result is -again- different when switching the Storage Engine to MyISAM where the correct Index is selected but still the Query is a DEPENDENT_SUBQUERY.
Is there any workaround?
nice Greetings.
3Bit
(This was tested on Mysql 5.0.26 on 64Amd linux.)