MySQL improve query performance

Question

I have such a query:

SELECT city.id, city.country_id, localization.lang , localization.name, ... some other fields ...
FROM city city
LEFT OUTER JOIN city_localization localization ON ( localization.city_id = city.id )
WHERE city.country_id = '196' AND localization.lang = 'en'
ORDER BY localization.name

"city" table schema:

enter image description here

"city_localization" table schema:

enter image description here

Explain output:

enter image description here

How can I avoid using filesort and temporary?

I don't know about filesort and temporary, but I've noticed that you filter right side of left join, replacing it in effect with inner join. One should filter city_localization in join itself: LEFT OUTER JOIN city_localization localization ON ( localization.city_id = city.id and localization.lang = 'en'). — Nikola Markovinović
– Nikola Markovinović, Commented Apr 8, 2012 at 20:13

Federico Vera · Accepted Answer · 2012-04-08 20:51:09Z

1

You'll need to create an index for localization.city_id and another one for localization.name (you currently have one that also includes localization.lang).

If you are retrieving a lot of rows you might want to remove city.country_id and localization.lang from your select, after all you already have those values.

answered Apr 8, 2012 at 20:51

Federico Vera

1,3571 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL improve query performance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related