MySQL LEFT JOIN 该怎么优化，求解？

Question

语句：

SELECT `j`.`id`,`title`,`publisher_id`,`cert_type_id`,`cert_prof_id`,`need_province_id`,`need_city_id`,`contract_price`,`work_years`,`publish_at`,`cert_reg_state`,`cert_purpose`,`contract_years`,`state` ,`sort`FROM `build_job` `j` left JOIN (select info_id,province_id,sort,state from build_top where  province_id=1 and info_type=1 and end_at > 1508999126 ) t ON `j`.`id`=`t`.`info_id` WHERE `is_checked` = 1 AND `is_published` = 1 AND `status` = 1  ORDER BY `state` desc,`sort` desc,`publish_at` desc LIMIT 0,10

执行计划的结果：

这种情况我该往哪个方向进行优化？不论怎么添加索引左表都是全表扫描

Answer 1

alter table `build_job` add index `build_job_index_name` (`is_checked`, `is_published`, `status`);

添加以上索引可以避免全表扫描