温馨提示:本文翻译自stackoverflow.com,查看原文请点击:mysql - Slow triple inner join
mysql 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
被浏览
168
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

最新帖子

最新文章