MySQL Forums
Forum List  »  Optimizer & Parser

Re: Group by Optimization
Posted by: Björn Steinbrink
Date: December 03, 2005 06:07AM

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.

Options: ReplyQuote

Written By
December 01, 2005 08:27AM
Re: Group by Optimization
December 03, 2005 06:07AM
December 05, 2005 11:32AM
December 09, 2005 04:53PM
December 05, 2005 11:29AM
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.