sql - 改进这个可能的5连接SQL语句

Question

我使用的是MySQL 5，我需要执行这句话才能得到结果。回想起来，这还是第一次尝试。我知道有多种方法可以改进它，但我想先征求您的意见:

SELECT p.id, p.image, p.lat, p.lng, 
       p.category_id, p2.title, p2.description, c2.name
FROM place p
LEFT JOIN place_translation p2 ON p.id = p2.id
LEFT JOIN trip_place t ON p.id = t.place_id
LEFT JOIN category c ON p.category_id = c.id
LEFT JOIN category_translation c2 ON c.id = c2.id
WHERE c.root_id =1
AND c2.lang =  'en'
AND p2.lang =  'en'
AND t.trip_id =1
AND p.root_id =1
AND p.lft >39
AND p.rgt <44
ORDER BY p2.title

这是一项多语言服务，因此我们有 place_translation 和 category_translation。我们获取属于一次旅行和属于一个城市的地点(这是一个嵌套集，因此 root_id、lft 和 rgt)

索引是:

on table place: id, category_id
on table place_translation: id-lang index
on table trip_place: place_id, trip_id
on table category: id
on table category_translation: id-lang index

那么，根据您的经验，您将如何改进它以使其更小？我知道在出现问题之前我不必非规范化；但我想打好基础。

谢谢!

Answer 1

有两件事是显而易见的。由于您对 WHERE 中每个表的值都有条件，因此您需要在 JOIN 子句中找到匹配的行。这意味着您应该使用内部联接，而不是左外部联接。接下来，要尽快过滤结果，您可以将一些条件从 WHERE 移动到 JOIN .. ON。

我还以一种对我来说有意义的方式对表格进行了重新排序，即首先您从 trip_place 中进行选择，因为您知道您只想要这次旅行中的地点。然后，为 trip_place 中的每一行查找匹配的 place，并为每个 place 查找匹配的 category。翻译是最后的，因为它们不会影响查询的功能。所以查询将是这样的:

SELECT
    p.id, p.image, p.lat, p.lng, 
    p.category_id, p2.title, p2.description, c2.name
FROM
    trip_place t
    JOIN place p ON p.id = t.place_id AND p.root_id = 1 AND p.lft > 39 AND p.rgt < 44
    JOIN category c ON p.category_id = c.id AND c.root_id = 1
    JOIN place_translation p2 ON p.id = p2.id AND p2.lang = 'en'
    JOIN category_translation c2 ON c.id = c2.id AND c2.lang = 'en'
WHERE
    t.trip_id = 1
ORDER BY p2.title

我无法判断它是否真的更快，但可能是。您应该对它们都运行EXPLAIN。从索引列表来看，您可能应该在 place (root_id, p.lft, p.rgt) 添加一个索引(所有索引都作为单个索引，而不是单独的每一列)。