MySQL 5.5 Query Optimizer - Massive Regression (vs 5.0)

[Chris Ricks]

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.