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