mysql - query with LEFT JOIN and ORDER BY...LIMIT slow, uses Filesort -

i have following query:

select      fruit.date,     fruit.name,     fruit.reason,     fruit.id,     fruit.notes,     food.name     fruit  left join     food_fruits ff on fruit.fruit_id = ff.fruit_id , ff.type='fruit'  left join     food using (food_id)  left join     fruits_sour fs on fruits.id = fs.fruit_id     (fruit.date < date_sub(now(), interval 180 day))         , (fruit.`status` = 'rotten')         , (fruit.location = 'usa')         , (fruit.size = 'medium')         , (fs.fruit_id null) order `food.name` asc limit 15 offset 0 

and indexes ever want, including following being used:

fruit        - fruit_filter (size, status, location, date) food_fruits  - food_type (type) food         - food (id) fruits_sour  - fruit_id (fruit_id) 

i have indexes thought work better not being used:

food_fruits  - fruit_key (fruit_id, type) food         - id_name (food_id, name) 

the order by clause causing temporary table , filesort used, unfortunately. without that, query runs lickety-split. how can query not need filesort? missing?

edit:

the explain:

the reason order by clause done on field not part of index used query. engine can run query using fruit_filter index, has sort on different field, , that's when filesort comes play (which means "sort without using index", reminder in comments).

i don't know times getting result, if difference lot, create temporary table intermediate results , sorted afterwards.

(by way, not sure why use left join instead of inner join , why use food_fruits - answered in comments)

update.

try subquery approach, may (untested), splits sorting pre-filtering:

select     fr.date,     fr.name,     fr.reason,     fr.id,     fr.notes,     food.name   (   select      fruit.date,     fruit.name,     fruit.reason,     fruit.id,     fruit.notes,       fruit   left join     fruits_sour fs on fruit.id = fs.fruit_id       (fruit.date < date_sub(now(), interval 180 day))         , (fruit.`status` = 'rotten')         , (fruit.location = 'usa')         , (fruit.size = 'medium')         , (fs.fruit_id null)   ) fr left join     food_fruits ff on fr.fruit_id = ff.fruit_id , ff.type='fruit' left join     food using (food_id)   order `food.name` asc limit 15 offset 0