mysql - 慢速三重内部联接

我有下面的查询（名字被修改了），它真的很慢。我不知道是因为写得更好还是因为我缺少索引，所以写得这么慢。另外，我应该如何创建索引，因为大多数连接都在虚表上？

select y.radish, g.enton
from great g
inner join(
    select sr.radish, sr.greatReferenceID
    from spaceRadish sr
    inner join(
        select s.id
        from super s
        inner join experiments e
        on s.CID = e.analysis) x
    on sr.springID = x.id) y
on g.id = y.greatReferenceID

explain select的输出：

'1', 'PRIMARY', '<derived2>', 'ALL', NULL, NULL, NULL, NULL, '14085960', ''
'1', 'PRIMARY', 'g', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'y.greatReferenceID', '1', ''
'2', 'DERIVED', '<derived3>', 'ALL', NULL, NULL, NULL, NULL, '287', ''
'2', 'DERIVED', 'sr', 'ref', 'springID', 'springID', '4', 'x.id', '831666', ''
'3', 'DERIVED', 'e', 'ALL', NULL, NULL, NULL, NULL, '3271', ''
'3', 'DERIVED', 's', 'ref', 'CID,CID_2', 'CID', '767', 'cpp.e.analysis', '16', 'Using where; Using index'

最佳答案

尽量避免子查询

    select y.radish, g.enton
    from great g
    inner join spaceRadish sr ON  sr.greatReferenceID = g.id
    inner join super s  s.id = sr.springID
    inner join experiments e on s.CID = e.analysis

确保你有正确的索引

    table  great  composite index on (id, enton)
    table spaceRadish composite index on (greatReferenceID, springID)
    table super cmposite index on (id, cid)
    table experiments index  on analysis

关于mysql - 慢速三重内部联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/56872591/