Re: Group by Optimization

[Björn Steinbrink]

Looks like your IDX3_PROPSTATE index does not cover PROP_STATE and PROP_CITY.
AFAIK MySQL does not use multiple keys when searching a single table, so you'd need an index that covers both, PROP_STATE and PROP_CITY, otherwise MySQL creates a temporary table with all rows that it can find using an index and does a table scan on the resulting table which holds about 3.7 million rows according to your EXPLAIN output, which creates some io action. ;)
I assume that DB2 does use more than one key at a time and thus is faster.