温馨提示:本文翻译自stackoverflow.com,查看原文请点击:mysql - Slow triple inner join

mysql - 慢速三重内部联接

发布于 2020-03-27 11:23:36

我有以下查询(名称已更改),而且确实很慢。我不知道它是否这么慢,因为它可以写得更好,或者因为我缺乏索引。另外,由于大多数联接都在虚表上,因此我应该如何创建索引?

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

解释选择的输出:

'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'

查看更多

查看更多

提问者
LizzAlice
被浏览
90
scaisEdge 2019-07-03 22:57

尽量避免子查询

    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

发布
问题

分享
好友

手机
浏览

扫码手机浏览