mysql - Processing a couple million records per table, the query seems t be working and optimized but takes a lengthy time for execution -

is there way change or optimize query spedd execution time?

select      max(u.phone_mobile) mobile_number      ccr_prod.ccr_unique_member u  inner join ccr_prod.ccr_member m      on u.ccif = m.ccif , u.first_open_date = m.first_open_date m.source_id not in ('fpt', 'vlink')      , u.phone_mobile not in ('', '0')     , datediff(curdate(), u.first_open_date) > 120 group      u.ccif 

the thing can suggest (move condition m table subquery , call needed field there):

select      max(u.phone_mobile) mobile_number      ccr_prod.ccr_unique_member u  inner join (      select        ccif, first_open_date            ccr_prod.ccr_member             source_id not in ('fpt', 'vlink') ) m    on u.ccif = m.ccif , u.first_open_date = m.first_open_date  u.phone_mobile not in ('', '0')     , datediff(curdate(), u.first_open_date) > 120 group      u.ccif 

and if necessary optimize more, can try not find max limit 1 if field phone_mobile index faster:

select      u.phone_mobile mobile_number      ccr_prod.ccr_unique_member u  inner join (      select        ccif, first_open_date            ccr_prod.ccr_member             source_id not in ('fpt', 'vlink') ) m    on u.ccif = m.ccif , u.first_open_date = m.first_open_date  u.phone_mobile not in ('', '0')     , datediff(curdate(), u.first_open_date) > 120 oredr     u.phone_mobile desc  limit 1 

by way can improve performance if replace condition:

datediff(curdate(), u.first_open_date) > 120 

with constant prepared like:

u.first_open_date < '2014-12-20 00:00:00'