Re: Group by Optimization

[Timour Katchaounov]

Hi,

Hope this reply is not too late. In order to speed-up such queries you need to
create an index on the columns (PROP_STATE, PROP_CITY). In this case
MySQL can avoid creating a temporary table and sorting it because the
index is alredy sorted.

This is a simple example that demonstrates this behavior (plese check my
comments after each EXPLAIN):

create table xyz (c1 int, c2 int);
insert into xyz values (1,2);
insert into xyz values (1,3);
insert into xyz values (2,3);
insert into xyz values (2,4);
insert into xyz values (3,7);
explain select c1 from xyz where c2 = 3 group by c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: xyz
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where; Using temporary; Using filesort
1 row in set (0.01 sec)

NOTICE: this is the worst case - no indexes => full table scan + file sort

create index c2i on xyz (c2);
explain select c1 from xyz where c2 = 3 group by c1;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: xyz
type: ref
possible_keys: c2i
key: c2i
key_len: 5
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
1 row in set (0.02 sec)

NOTICE: this is your case above => using index to filter rows, but still have to
perform file sort.

create index c2c1i on xyz (c2,c1);
explain select c1 from xyz where c2 = 3 group by c1;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: xyz
type: ref
possible_keys: c2i,c2c1i
key: c2c1i
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.02 sec)

NOTICE: this is what you need => using the index to both filter rows, and to avoid
sorting. Pay attention to the order of fields in the index.


Let me know what was the speedup (if this suggestion helped),

Timour