optimization - Optimizing a MySQL query with a large IN() clause or join on derived table -

let's need query associates of corporation. have table, "transactions", contains data on every transaction made.

create table `transactions` (   `transactionid` int(11) unsigned not null,   `orderid` int(11) unsigned not null,   `customerid` int(11) unsigned not null,   `employeeid` int(11) unsigned not null,    `corporationid` int(11) unsigned not null,   primary key (`transactionid`),   key `orderid` (`orderid`),   key `customerid` (`customerid`),   key `employeeid` (`employeeid`),   key `corporationid` (`corporationid`) ) engine=myisam default charset=utf8; 

it's straightforward query table associates, there's twist: transaction record registered once per employee, , there may multiple records 1 corporation per order.

for example, if employees , b corporation 1 both involved in selling vacuum cleaner corporation 2, there 2 records in "transactions" table; 1 each employee, , both corporation 1. must not affect results, though. trade corporation 1, regardless of how many of employees involved, must treated one.

easy, thought. i'll make join on derived table, so:

select corporationid transactions join (select distinct orderid transactions corporationid = 1) foo using (orderid) 

the query returns list of corporations have been involved in trades corporation 1. that's need, it's slow because mysql can't use corporationid index determine derived table. understand case subqueries/derived tables in mysql.

i've tried query collection of orderids separately , use ridiculously large in() clause (typhically 100 000+ ids), turns out mysql has issues using indices on ridiculously large in() clauses , result query time not improve.

are there other options available, or have exhausted them both?

if understand requirement, try this.

select distinct t1.corporationid transactions t1 exists (     select 1     transactions t2     t2.corporationid =  1     , t2.orderid = t1.orderid) , t1.corporationid != 1; 

or this:

select distinct t1.corporationid transactions t1 join transactions t2 on t2.orderid = t1.orderid , t1.transactionid != t2.transactionid t2.corporationid = 1 , t1.corporationid != 1;