MySQL Forums
Forum List  »  Optimizer & Parser

Optimizer misbehaves
Posted by: Drei Bit
Date: July 04, 2007 05:16AM

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.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizer misbehaves
3242
July 04, 2007 05:16AM
2296
July 04, 2007 07:26AM
2318
July 04, 2007 10:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.