CODEWITHSUNDEEP
mysqloptimizationquery-optimization
breethe
breethe

Reputation: 613

MySQL improve query performance

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?

Upvotes: 0

Views: 300

Answers (1)

Federico Vera
Federico Vera

Reputation: 1357

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.

Upvotes: 1

Related Questions