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

---

多嵌套了一层是没必要的，然后用explain看看哪里需要加索引呢？或者贴一下explain的结果呢

---

1、只联合查询变化频繁和结合紧密的数据，那种不太变化的数据，扔到一个key-value数组，在显示的时候用key显示关联的value。
2、该冗余的数据冗余起来，比如在a表里放一个 c.name，就少了c这个联表查询。
3、你给出的SQL中，最外层的括号f没必要，内层SQL就可以排序并得到想要的数据了。
4、最后把精简过的SQL explain一下，给关键的列加上索引。