MySQL Forums
Forum List  »  Optimizer & Parser

MySQL 5.5 Query Optimizer - Massive Regression (vs 5.0)
Posted by: Chris Ricks
Date: February 02, 2012 01:17AM

Hi all,

I'm doing some testing against MySQL 5.5.19 and have noticed some worrying regressions against 5.0.84 with regard to query evaluation.

The table at the core of the problem contains about 50 million rows and has 3 columns of interest:

* client_id - A 5 to 10 character string referenced with 'LIKE ' in where clauses.
* trx_time - A timestamp column referenced with 'BETWEEN ' in where clauses.
* settlement - A 'YYYYMMDD' value referenced with 'BETWEEN ' and '= ' in where clauses.

There are 3 indices of interest:

IX_client_time - covering (client_id, trx_time)
IX_time_client - covering (trx_time, client_id)
IX_settlement_client - covering (settlement, client_id)

In MySQL 5.0.84, queries supplied with a client_id AND a trx_time value would use the IX_client_time index, which is definitely optimal. In 5.5.19, the IX_time_client index is always used unless an index hint is provided.

In MySQL 5.0.84, queries supplied with a client_id AND a settlement value would use the IX_settlment_client index, which is definitely optimal. In 5.5.19, the IX_client_time index is used unless an index hint is provided.

These changes result in query evaluation times being terribly impacted and I'd really prefer to not start littering our queries with index hints.

No tweaks to anything beyond InnoDB buffer size have been made on either installation, and a comparison of common variables shows that the two instances are configured otherwise identically. In both cases, the tables are using the InnoDB engine.

The data set and queries used in each case are identical and using ANALYZE TABLE results in no changes.

Any and all suggestions appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL 5.5 Query Optimizer - Massive Regression (vs 5.0)
2494
February 02, 2012 01:17AM


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.