mysql 优化问题？

Question

sql语句如下

SELECT
        `ap_log`.`id`,
        `company_title`,
        `contact_name`,
        `batch_id`,
        `item_id`,
        `item_title`,
        `item_sku`,
        `number`,
        `ap_log`.`create_time` 
FROM
        `ap_item_log` AS `ap_log`
        LEFT JOIN `ap_seller_company` AS `ap_company` ON `ap_log`.`seller_id` = `ap_company`.`seller_id` 
        AND `ap_log`.`uid` = `ap_company`.`uid` 
WHERE
         `ap_log`.`puid` = '2' AND `ap_log`.`seller_id` = 2 AND `ap_log`.`type` = 1 and `ap_company`.`status` = 2
ORDER BY
        `ap_log`.`id` DESC,
        `ap_log`.`create_time` DESC 
        LIMIT 15 OFFSET 0

以上sql语句执行花了

使用索引情况：

两张表索引情况
ap_item_log表：

ap_seller_company表：

当我把order by 条件删除，就变得很快了，只用了0.028秒，使用的索引情况如下

这样的sql该怎么优化呢，求解答。

Answer 1

思路 小表驱动大表查询

SELECT  
    b.`id`,
   `company_title`,
    `contact_name`,
    `batch_id`,
    `item_id`,
    `item_title`,
    `item_sku`,
    `number` ,
     b.`create_time` 
    from `ap_company`  left join 
(SELECT `ap_log`.`id`,ap_log`.`seller_id`,`ap_log`.`uid`,`ap_log`.`create_time` 
    from  `ap_item_log` AS `ap_log` 
    where   `ap_log`.`puid` = '2' AND `ap_log`.`seller_id` = 2 AND `ap_log`.`type` = 1 and `ap_company`.`status` = 2 
    ORDER BY
    `ap_log`.`id` DESC,
    `ap_log`.`create_time` DESC 
    LIMIT 15 OFFSET 0 ) as b    ON b.`seller_id` = `ap_company`.`seller_id`  
    AND b.`uid` = `ap_company`.`uid`