MySQL Bugs: #81914: The optimizer is choosing the wrong index

Bug #81914	The optimizer is choosing the wrong index
Submitted:	17 Jun 2016 22:42	Modified:	18 Aug 2016 12:53
Reporter:	monty solomon	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7.12	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
We noticed some queries are taking significantly longer running on MySQL 5.7 than on MySQL 5.6.

According to the EXPLAIN output, MySQL is choosing the wrong index on both MySQL 5.6 and MySQL 5.7.

When the index that the optimizer chooses is used on MySQL 5.7 the query takes 40+ seconds instead of a fraction of a second with the better index.

How to repeat:
In the poorly performing query one of the predicates in the WHERE clause appears in the attached_condition in the EXPLAIN output.

In the well performing query the predicate appears in the index_condition in the EXPLAIN output.

EXPLAIN output using the poor index

*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.41"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "feed",
        "partitions": [
          "p0",
          "p201312",
          "p201401",
          "p201402",
          "p201403",
          "p201404",
          "p201405",
          "p201406",
          "p201407",
          "p201408",
          "p201409",
          "p201410",
          "p201411",
          "p201412",
          "p201501",
          "p201502",
          "p201503",
          "p201504",
          "p201505",
          "p201506",
          "p201507",
          "p201508",
          "p201509",
          "p201510",
          "p201511",
          "p201512",
          "p201601",
          "p201602",
          "p201603",
          "p201604",
          "p201605",
          "p201606",
          "p201607",
          "p201608",
          "p201609",
          "p201610",
          "p201611",
          "p201612",
          "p201701",
          "p99999999"
        ],
        "access_type": "range",
        "possible_keys": [
          "pfcmKey",
          "feed_acct_interactionType",
          "feed_interactionType_only",
          "feed_acct_only",
          "feed_all",
          "feed_channel_interactionType",
          "feed_channel_only"
        ],
        "key": "feed_all",
        "used_key_parts": [
          "portalId",
          "updatedAt"
        ],
        "key_length": "12",
        "rows_examined_per_scan": 1,
        "rows_produced_per_join": 0,
        "filtered": "50.00",
        "index_condition": "((`SocialFeed`.`feed`.`portalId` = 1) and (`SocialFeed`.`feed`.`updatedAt` > 1466129797507))",
        "cost_info": {
          "read_cost": "2.31",
          "eval_cost": "0.10",
          "prefix_cost": "2.41",
          "data_read_per_join": "200"
        },
        "used_columns": [
          "id",
          "portalId",
          "accountGuid",
          "interactionType",
          "updatedAt",
          "createdAt",
          "feedKey",
          "data",
          "month",
          "readAt",
          "channelKey"
        ],
        "attached_condition": "(`SocialFeed`.`feed`.`channelKey` in ('FacebookPage:2','Twitter:3','FacebookPage:4','FacebookPage:5','FacebookPage:6','Twitter:7','FacebookPage:8'))"
      }
    }
  }
}
1 row in set, 1 warning (0.01 sec)

EXPLAIN output using the better index

*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "23.81"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "7.00"
      },
      "table": {
        "table_name": "feed",
        "partitions": [
          "p0",
          "p201312",
          "p201401",
          "p201402",
          "p201403",
          "p201404",
          "p201405",
          "p201406",
          "p201407",
          "p201408",
          "p201409",
          "p201410",
          "p201411",
          "p201412",
          "p201501",
          "p201502",
          "p201503",
          "p201504",
          "p201505",
          "p201506",
          "p201507",
          "p201508",
          "p201509",
          "p201510",
          "p201511",
          "p201512",
          "p201601",
          "p201602",
          "p201603",
          "p201604",
          "p201605",
          "p201606",
          "p201607",
          "p201608",
          "p201609",
          "p201610",
          "p201611",
          "p201612",
          "p201701",
          "p99999999"
        ],
        "access_type": "range",
        "possible_keys": [
          "feed_channel_only"
        ],
        "key": "feed_channel_only",
        "used_key_parts": [
          "portalId",
          "channelKey",
          "updatedAt"
        ],
        "key_length": "54",
        "rows_examined_per_scan": 7,
        "rows_produced_per_join": 7,
        "filtered": "100.00",
        "index_condition": "((`SocialFeed`.`feed`.`portalId` = 1) and (`SocialFeed`.`feed`.`channelKey` in ('FacebookPage:2','Twitter:3','FacebookPage:4','FacebookPage:5','FacebookPage:6','Twitter:7','FacebookPage:8')) and (`SocialFeed`.`feed`.`updatedAt` > 1466129797507))",
        "cost_info": {
          "read_cost": "15.41",
          "eval_cost": "1.40",
          "prefix_cost": "16.81",
          "data_read_per_join": "2K"
        },
        "used_columns": [
          "id",
          "portalId",
          "accountGuid",
          "interactionType",
          "updatedAt",
          "createdAt",
          "feedKey",
          "data",
          "month",
          "readAt",
          "channelKey"
        ]
      }
    }
  }
}
1 row in set, 1 warning (0.00 sec)

Hi!

Thank you for reporting a bug to us. However, what we require here are repeatable test cases. Hence, we can not work on the bug, unless we can repeat it and to do that we need the schema AND the contents of the table(s) that you used in that query.

There is yet another question. If you FORCE the index that performs better, what is a difference in the speed ???

Thanks in advance.

On MySQL 5.6 the query takes 0.1 seconds with or without the USE INDEX.

On MySQL 5.7 the query takes 0.1 seconds with USE INDEX and 1 min 23.49 sec without USE INDEX.

Hi Mr. Solomon,

Just upload the data with the private comment. Data will be visible only to MySQL developers.

Will try to reproduce with non-sensitive data

Thank you for your effort. But, what ever you do, make sure that you supply us with sufficient info and data so that we can reproduce it.

I plan to provide sufficient data and details so you can reproduce. It may take a while for me to put it all together.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".