Assume I have an existing table of "items", containing cities, states and countries distinguished by the field "type" which contains "City", "State" or "Country". A field "parent_id" of a city either points to a "State" entry or to a "Country" entry (if the country does not have states).
I have another table of "local_names" where some of the above items have corresponding local names, others don't.
I want to get all items ordered by their local name (or their "original" name if local is missing) in the order "City, Country" (also when state is available).
id type name parent_id ...
1 City Detroit 2
2 State Michigan 3
3 Country USA
4 City Cologne 5
5 Country Germany
id item_id name
1 4 Koln
2 5 Deutschland
But the following performs quite poor. How could it be improved?
SELECT * FROM items
LEFT JOIN items AS parent ON parent.id = items.parent_id
LEFT JOIN items AS grandparent ON grandparent.id = parent.parent_id
LEFT JOIN local_names AS local_item_name ON local_item_name.item_id = items.id
LEFT JOIN local_names AS local_parent_name ON local_parent_name.item_id = parent.id
LEFT JOIN local_names AS local_grandparent_name ON local_grandparent_name.item_id = grandparent.id
ORDER BY coalesce(coalesce(local_grandparent_name.name, grandparent.name),