mysql order by 是怎么把临时表优化掉

Question

EXPLAIN SELECT o.*,a.account_name,a.mobile FROM
(SELECT * FROM m_order_reviewed WHERE is_reviewed = -1 AND is_delete=0) AS o
LEFT JOIN m_account AS a ON o.user_id=a.id INNER JOIN
(SELECT material_code FROM m_material_purchase WHERE purchaser = 2349 AND is_delete = 0)
b ON o.material_code = b.material_code order by o.id limit 0,10

显示出现了临时表
id 和 gmt_modified 都加上索引了

Answer 1

SELECT
    o.*,
    a.house_id,
    a.mobile 
FROM
    ( SELECT * FROM m_order_reviewed WHERE is_reviewed = - 1 AND is_delete = 0 ORDER BY id ) AS o
    LEFT JOIN m_account AS a ON o.user_id = a.id
    INNER JOIN ( SELECT material_code FROM m_material_purchase WHERE purchaser = 2349 AND is_delete = 0 ) b USING ( material_code ) 
    LIMIT 0,10

望采纳！！！