MySQL optimize complicate query NOT IN (2 SELECT WITH JOIN) WHERE -

MySQL optimize complicate query NOT IN (2 SELECT WITH JOIN) WHERE -

i have next query:

select driver_id, first_name, last_name drivers driver_id not in (select distinct w.driver_id waybills w bring together drivers d on d.driver_id = w.driver_id w.waybill_owner = 1 , w.waybill_status = 'dispatched' , w.delivery_date = '2014-10-28') , driver_id not in (select distinct wm.driver_id waybill_movements wm bring together drivers d on d.driver_id = wm.driver_id wm.movement_owner = 1 , wm.delivery_date = '2014-10-28') , status = 'active' , driver_owner = 1 order last_name asc

how can optimize query?

query works , homecoming expected results question if query can optimized.

many time , help.

update:

and, yes, have these indexes:

waybills(waybill_owner, waybill_status, w.delivery_date) waybill_movements (wm.movement_owner, delivery_date) drivers(driver_id primary key , drivers(status, driver_owner)

the table construction not need optimization

i didn't expect have many answers. give thanks all.

i have typically found doing left-joins , looking null of table result easier , great utilization of indexes can made.

the drivers table have index on (driver_owner, status, driver_id) waybill table, index on(waybill_owner, driver_id, delivery_date, waybill_status) waybill_movements, index on(movement_owner, driver_id, delivery_date ) select d1.driver_id, d1.first_name, d1.last_name drivers d1 left bring together waybills w on d1.driver_id = w.driver_id , d1.driver_owner = w.waybill_owner , w.waybill_status = 'dispatched' , w.delivery_date = '2014-10-28' left bring together waybill_movements wm on d1.driver_id = wm.driver_id , d1.driver_owner = wm.movement_owner , wm.delivery_date = '2014-10-28' d1.driver_owner = 1 , d1.status = 'active' , w.driver_id null , wm.driver_id null order d1.last_name asc

by including "is null" waybill , waybill_movements, getting not have matching record.

also, seeing waybill_owner = 1... coincidence of driver_owner = 1 also? if waybill owner should same driver owner, alter index on waybill table have waybill_owner first, driver_id, rest , alter joins based on drivers.driver_owner = waybills.waybill_owner (similarly on waybill_movements)

revised per feedback

revised indexes , query bring together based on same drivers.driver_owner table waybill tables prevent false results of cross-owner matches whatever unusual possibility may exist.

mysql select join