mysql - Optimize sql query runtime -

i have query:

select `l`.`id`, `l`.`headline`, `l`.`description`, `l`.`image`, `l`.`campaign_id`, if(l.required_impressions=0,0,1) sequence, (ifnull(round(count(distinct(lc.id)) / count(distinct(li.id)), 3) * 100, 0) * 0.3) + (l.cost * 0.7) `scales`, `c`.`name` `campaign` `app_links` `l` inner join `app_campaigns` `c` on c.id = l.campaign_id left join `app_link_clicks` `lc` on lc.link_id = l.id left join `app_link_impressions` `li` on li.link_id = l.id left join `app_links_categories` `lcat` on l.id = lcat.link_id left join `app_links_countries` `lcou` on l.id = lcou.link_id lcat.category_id in(3,7,14) , lcou.country_id in(89,147,124,131,259,197,88) group `l`.`id` order sequence desc, `scales` desc limit 6 

the explain query return:

do have ideas how optimize query? take ~0.6 sec, it's quite long :/

as long you're referring lcat , lcou in where clause makes no sense them joined left join.

so proposal is:

1. change left join lcat , lcou inner join
2. move these 2 joins upper inner joins in beginning
3. move lcat , lcou conditions correspondent on clauses (this shouldn't change anything, more readable)
4. create composite link_id + country_id , link_id + category_id indexes
5. as can see - mysql optimizer changed order tables joins, app_links joined in middle , seems app_links myisam (it's guess based on fact campaign_id doesn't clustered index). if - try add compound campaign_id + id index well

ps: make changes above , provide new execution plan

pps: provide schema tables query