I have a database that has city/state data. I am making a webpage for each unique city/state combination. There are collisions in the unique names and when that occurs I want to choose the largest city with that name by default.
I have the following query I wrote but there are some penalty hits associated with it, including:
2x Using where
1x Using temporary
1x Using filesort
SELECT a.id, a.name, a.population
FROM geo_cities a
SELECT name, MAX(population) AS population
WHERE state = [numeric value]
GROUP BY name
) AS b ON a.name = b.name AND a.population = b.population
I was curious if anyone has any suggestions on how to mitigate these performance issues? Thanks!