mysql - How to avoid filesort when using left join and order by -

i have 3 tables: accounts, contacts , accounts_contacts (a mapping table). have abour1 million records in each of tables. query using filesort , takes more minute run:

explain select contacts.salutation salutation, contacts.first_name first_name, contacts.last_name last_name, contacts.title title, jt0_accounts.id account_id, jt0_accounts.name account_name  contacts  left join accounts_contacts jt1_accounts_contacts on (contacts.id = jt1_accounts_contacts.contact_id , jt1_accounts_contacts.deleted = 0)  left join accounts jt0_accounts on (jt0_accounts.id = jt1_accounts_contacts.account_id , jt0_accounts.deleted = 0)  order jt0_accounts.name desc; 

this explain output:

id  select_type table   type    possible_keys   key key_len ref rows    1    simple contacts    null    null    null    null    195634  using temporary; using filesort 1    simple jt1_accounts_contacts   ref idx_contid_del_accid    idx_contid_del_accid    113 sugar7.contacts.id,const    1    1   simple  jt0_accounts    eq_ref  primary,idx_accounts_id_del,idx_accounts_date_entered,idx_accnt_assigned_del    primary 108 sugar7.jt1_accounts_contacts.account_id 1    

as can see, contacts table using filesort on contacts table.

i've tried getting rid of filesort adding "where jt0_accounts.name <> ''" before "order by" becomes:

explain select contacts.salutation salutation, contacts.first_name first_name, contacts.last_name last_name, contacts.title title, jt0_accounts.id account_id, jt0_accounts.name account_name  contacts  left join accounts_contacts jt1_accounts_contacts on (contacts.id = jt1_accounts_contacts.contact_id , jt1_accounts_contacts.deleted = 0)  left join accounts jt0_accounts on (jt0_accounts.id = jt1_accounts_contacts.account_id , jt0_accounts.deleted = 0)  jt0_accounts.name <> '' order jt0_accounts.name desc; 

it rid of filesort on contacts table, it's using filesort on mapping table:

id  select_type table   type    possible_keys   key key_len ref rows    1   simple  jt1_accounts_contacts   idx_account_contact,idx_contid_del_accid    null    null    null    34994   using where; using temporary; using filesort 1   simple  jt0_accounts    eq_ref  primary,idx_accounts_id_del,idx_accounts_date_entered,idx_accnt_name_del,idx_accnt_assigned_del primary 108 sugar7.jt1_accounts_contacts.account_id 1   using 1   simple  contacts    eq_ref  primary,idx_contacts_id_del,idx_contacts_date_entered   primary 108 sugar7.jt1_accounts_contacts.contact_id 1   using 

the idx_account_contact index consists of account_id , contacts_id. i've tried adding them clause not seem make difference.

any suggestion appreciated. thanks.

there not can particularly query. but, if change query use inner join, might have chance:

select c.salutation, c.first_name, c.last_name, c.title,        a.id account_id, a.name account_name accounts join      accounts_contacts ac      on a.id = ac.account_id , a.deleted = 0      contacts c join      on c.id = ac.contact_id , ac.deleted = 0      order a.name desc; 

then, try following indexes: accounts(name, deleted, id), accounts_contacts(account_id, contact_id), , concats(contact_id, deleted).