slow corelated subquery, not sure how to rewrite

[Tait Larson]

I've got the following query that mysql will run slowly:

select city_code, attributes from marketdata_apartments a where create_time = (select max(create_time) from marketdata_apartments where city_code= a.city_code);

here's the explain:

+----+--------------------+-----------------------+------+---------------+---------+---------+---------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------------+------+---------------+---------+---------+---------------------+--------+-------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 216191 | Using where |
| 2 | DEPENDENT SUBQUERY | marketdata_apartments | ref | PRIMARY | PRIMARY | 122 | content.a.city_code | 2161 | Using index |
+----+--------------------+-----------------------+------+---------------+---------+---------+---------------------+--------+-------------+

Note mysql does a scan of the primary table.

I have the following indexes defined on the table

+-----------------------+------------+-------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------+------------+-------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| marketdata_apartments | 0 | PRIMARY | 1 | city_code | A | NULL | NULL | NULL | | BTREE | |
| marketdata_apartments | 0 | PRIMARY | 2 | create_time | A | 223505 | NULL | NULL | | BTREE | |
| marketdata_apartments | 1 | i_marketdata_apartments_create_time | 1 | create_time | A | 28 | NULL | NULL | | BTREE | |
+-----------------------+------------+-------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


It doesn't seem like a table scan would be necessary. For instance mysql can look at each city_code value using the city_code portion of the primary index. Can't it them find the largest


HOW CAN I REWRITE THE QUERY ABOVE TO BE FASTER? I know corelated subqueries in mysql are generally slow. Can using a derived table help in this case?

Any ideas?

Thanks,

Tait