Improve this possible 5 joins SQL sentence

Question

Hi everyone,

I'm using MySQL 5, and I need to do this sentence to get the results. It's the first attempt when I think about it. I know there are several ways to improve it, but I want to get your opinions first:

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

It's a multilingual service, so we have place_translation and category_translation. We get the places that belongs to a trip and that belongs to a city (it's a nested set, so root_id, lft and rgt)

The indexes are:

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

So, with your experience, how would you improve it to make it smaller? I know I don't have to denormalized until I have problems; but I want to fix a good base.

thanks!

Answer 1

There are two obvious things. Since you have conditions on values from each table in WHERE, you require that you found matching rows in the JOIN clauses. This means you should use inner joins, not left outer joins. Next, to filter the results as soon as possible you can move some of the conditions from WHERE to JOIN .. ON.

I've also reordered the tables in a way that makes sense to me, i.e. first you are selecting from trip_place, because you know you want only places from this trip. Then for each row from trip_place find the matching place, and for each place find the matching category. Translations are last because they don't affect the query functionally. So the query would be something like this:

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

I can't tell if it's actually faster, but it might be. You should run EXPLAIN on both of them. From the look at the list of indexes, you should probably add an index on place (root_id, p.lft, p.rgt) (all of them as a single index, not each column individually).