Thread: Indexes and subqueries

Indexes and subqueries

From
Anton de Wet
Date:
I'm using 6.4.2 and I have the same problem as noted in previous mails in
the list archives but I can't find a coherent answer to the problem.

I need:

select * from sometable
  where somefield in (select anotherfield from anothertable)

if I do:

select * from sometable where somefield in ('a','b','c');

explain uses the indexes i have created on sometable.somefield, when I use
the subquery a linear search is done.

Up till now I have handeled this by running the subquery in perl or tcl
and creating the quoted list and this works fine. However, now I have a
case where the query created in this way is longer than the 8192 bytes
allowed.

Now I need one of 2 solutions:

  1) Is there any way I can let the subquery use the index?

    or

  2) How difficult/safe is it to change the max query length?

Your help will he HIGHLY appreciated!

Anton


--------------------------------------------------------------------
"And I'm told we [geeks and suits] do share some common rituals. Our
'flame war' is apparently held in person in their land and
called 'project meeting'." --- Alan Cox