mysql在连多表的情况下where优化的问题。

SELECT
        *
FROM
        (
                SELECT
                        a.id,
                        c.name AS external_name,
                        b.customer_number,
                        b.name,
                        a.number,
                        d.number as order_number,
                        e.code,
                        e.name as stock_name,
                        a.completed_at,
                        a.trade_count,
                        a.trade_price,
                        a.trade_count * a.trade_price as trade_amount
                FROM
                        trades a,
                        internal_accounts b,
                        external_accounts c,
                        orders d,
                        stocks e
                WHERE
                        a.internal_account_id = b.id
                AND c.id = a.external_account_id
                AND b.state != 8
                AND d.id = a.order_id
                AND e.code = d.stock_code
        ) AS f
ORDER BY
        f.id DESC

这种情况下如何做到最优啊Orz。
全局连表得查10s,数据大概12w的样子。

采用外连可以快一半的样子,但是感觉还不够快。

explain SELECT
    a.id,
    c.name AS external_name,
    b.customer_number,
    b.name,
    a.number,
    d.number AS order_number,
    e.code,
    e.name AS stock_name,
    a.completed_at,
    a.trade_count,
    a.trade_price,
    a.trade_count * a.trade_price AS trade_amount
FROM
    trades a
LEFT JOIN internal_accounts b ON a.internal_account_id = b.id
LEFT JOIN external_accounts c ON c.id = a.external_account_id
LEFT JOIN orders d ON d.id = a.order_id
LEFT JOIN stocks e ON e. CODE = d.stock_code
WHERE
    b.state != 8
ORDER BY
    a.id DESC
查看回复