MySQL 5.7 – Index Condition Pushdown Optimization Bug

MySQL 5.7 – Index Condition Pushdown Optimisation Bug

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. It uses the indexed columns to determine if they should retrieve the row instead of retrieving the entire table then filtering off the unnecessary rows.

Disable ICP if you are running a query with ORDER BY DESC against partitioned tables. It slows down the query considerably. You will definitely hit this bug if you are running zabbix server on MySQL database.

To disable ICP, use the following:

SET optimizer_switch='index_condition_pushdown=off';

Hope this helps!

Regards,
Wei Shan

References:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s