MySQL Forums
Forum List  »  Optimizer & Parser

Re: Group by Optimization
Posted by: Jay Pipes
Date: December 05, 2005 11:32AM

Bj�rn Steinbrink wrote:
> 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 ...

In MySQL 5, the index_merge optimization is used for some query patterns like WHERE col1='A' OR col2='B' in order to use two indexes during the execution, but only, of course, if indexes are available on both col1 and col2.

> I assume that DB2 does use more than one key at a
> time and thus is faster.

This is not necessarily true. Most likely, the issue is that the OP has put an index on only state, and not state and city, but in DB2 the index may include both fields... ?

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Views
Written By
Posted
6737
December 01, 2005 08:27AM
3679
December 03, 2005 06:07AM
Re: Group by Optimization
3298
December 05, 2005 11:32AM
3170
December 09, 2005 04:53PM
3135
December 05, 2005 11:29AM
7329
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.