MySQL Forums
Forum List  »  Optimizer & Parser

'where' with 'in' doesn't use index?
Posted by: Vlado Kurelec
Date: January 24, 2006 05:25AM

I have tables

a (a_id int, a_val varchar(60)) 4000000 rows
b (b_id int, b_val varchar(60)) 800000 rows

and indexes

a0 on a (a_id)
a1 on a (a_val)
b0 on a (b_id)
b1 on a (b_val)

select b_id from b
where b_val=(select min(concat('001',a_id)) from a where a_val like 'Ant%')

uses b1 index

select b_id from b
where b_val in (select min(concat('001',a_id)) from a where a_val like 'Ant%')

doesn't

of course, I want to use

select b_id from b
where b_val=(select concat('001',a_id) from a where a_val like 'Ant%')


I've tried
optimizer_prune_level = 0
force key (b1)
rewriting to join
but had no luck.

I'm porting from DB2 V5, it's visual explain tool shows that first temp table is created based on index a1 and then b1 is used to fetch from b.


Any idea?

Options: ReplyQuote


Subject
Views
Written By
Posted
'where' with 'in' doesn't use index?
3086
January 24, 2006 05:25AM


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.