MySQL Forums
Forum List  »  Optimizer & Parser

Re: Group by Optimization
Posted by: Timour Katchaounov
Date: December 14, 2005 02:36AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
6561
December 01, 2005 08:27AM
3611
December 03, 2005 06:07AM
3181
December 05, 2005 11:32AM
3084
December 09, 2005 04:53PM
3074
December 05, 2005 11:29AM
Re: Group by Optimization
7191
December 14, 2005 02:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.