Countries/States/Cities/Stores query optimization

[lehrlize]

Hi to all,

I want to build an geographical tree with countries, states, cities and stores.

I'm using this query:

SELECT countries.IDCountry, countries.NameCountry, states.IDState, states.NameState, cities.IDCity, 
cities.NameCity, stores.IDStore, stores.NameStore 
FROM countries 
LEFT JOIN states ON states.Country=countries.IDCountry 
LEFT JOIN cities ON cities.State=states.IDState 
LEFT JOIN stores ON stores.City=cities.IDCity 
ORDER BY countries.NameCountry, states.NameState, cities.NameCity, stores.NameStore 

Any ideas to improve the execution?

Thanks

[ccalender]

I would try the following:

Sometimes this does not affect the outcome of your join, but sometimes it does. It kind of depends on how your tables are set up and what is in them. But if it returns the same results, then you know it will be good.


SELECT countries.IDCountry, countries.NameCountry, states.IDState, states.NameState, cities.IDCity,
cities.NameCity, stores.IDStore, stores.NameStore
FROM countries, states, cities, stores
WHERE states.Country=countries.IDCountry
AND cities.State=states.IDState
AND stores.City=cities.IDCity
ORDER BY countries.NameCountry, states.NameState, cities.NameCity, stores.NameStore