Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Help Optimizing within Group Aggregate Query

raymondraymond EntrantCurrent User Role Beginner
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
JOIN
(
SELECT name, MAX(population) AS population
FROM geo_cities
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!

Comments

  • jrabbitjrabbit Contributor Inactive User Role Beginner
    What indexes do you have. You definintely need a non-unique index on name, and possibly a two column (name,population) may help.

    How volatile is your data? Maybe you should just run the query once to populate another table if your city/population data rarely changes, and reexecute it after an update, rather than computing this on the fly.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.