mysql - 使用多个联接优化sql select

如何优化此mysql语句？

SELECT DISTINCT p.name
FROM Something_Meta s1
JOIN Something_Meta s2 ON s1.fk_somethingId = s2.fk_somethingId
JOIN Products p ON s2.fk_productId = p.id
JOIN
(
     select fk_id from Restricted where fk_foo != 233 and fk_id NOT IN
        (
            Select fk_id  from Restricted where  fk_foo = 233
        )

)
r ON r.fk_id = p.id
WHERE s1.fk_somethingId = 63 AND s2.fk_somethingId <> s1.fk_somethingId

order by p.name ASC

我的桌子是这样的

Product (id,name )
Restricted (id,fk_id,fk_foo )
Something_Meta (id,fk_id,fk_somethingId )

fk_id是产品（id）的外键

可能是sql语句需要优化。

 select fk_id from Restricted where fk_foo != 233 and fk_id NOT IN
    (
        Select fk_id  from Restricted where  fk_foo = 233
    )

整个查询语句需要1.5秒钟以上的时间来运行，对于单个查询的网站而言，这需要花费很多秒的时间。

最佳答案

您可以尝试在(fk_foo, fk_id)上建立覆盖整个查询的索引：

create index ix_restricted_fk_foo_fk_id(fk_foo, fk_id) on restricted