Home MySQL how to avoid many joins in a complex sorting of a table?
Reply: 0

MySQL how to avoid many joins in a complex sorting of a table?

user1744
1#
user1744 Published in May 22, 2018, 11:25 pm

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).

table items

id type    name         parent_id ...
 1 City    Detroit      2
 2 State   Michigan     3
 3 Country USA
 4 City    Cologne      5
 5 Country Germany

table local_names

id item_id name
 1      4  Koln
 2      5  Deutschland

Wanted result:

Detroit, USA
Koln, 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),
                  coalesce(local_parent_name.name, parent.name), 
                  coalesce(local_item_name.name, items.name)),
         coalesce(local_item_name.name, items.name)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.304385 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO