# Help Optimizing within Group Aggregate Query **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [December 21, 2007, 3:28am UTC](https://forums.percona.com/t/help-optimizing-within-group-aggregate-query/575 "2007-12-21T03:28:46Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![raymond](https://avatars.discourse-cdn.com/v4/letter/r/c57346/32.png) [@raymond](https://forums.percona.com/u/raymond)\ **Post date:** [December 21, 2007, 3:28am UTC](https://forums.percona.com/t/help-optimizing-within-group-aggregate-query/575/1 "2007-12-21T03:28:46Z")
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! ---
**Author:** ![jrabbit](https://avatars.discourse-cdn.com/v4/letter/j/a587f6/32.png) [@jrabbit](https://forums.percona.com/u/jrabbit)\ **Post date:** [December 22, 2007, 4:53pm UTC](https://forums.percona.com/t/help-optimizing-within-group-aggregate-query/575/2 "2007-12-22T16:53:37Z")
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.