we've got one old database running MySQL 5.1. We now want to migrate it to MySQL 5.7 but some queries that worked okay are suddenly very very slow (by a factor of 60 or more slower).

The INNODB table in question (EVENT) has amongst other columns a COMPANY\_ID (foreign key to a COMPANY table) and EVENT\_DATETIME of type DATETIME. There is an index on COMPANY\_ID, EVENT\_DATETIME and for testing I've added one EVENT\_DATETIME, COMPANY\_ID.
Currently basically all EVENTs have the COMPANY\_ID 1 but this will change.

We have a count query to query the number of events in the last year:

```
select count(distinct this_.EVENT_ID) as y0_ from EVENT this_
       where this_.EVENT_DATETIME>='2018-10-22 00:00:00'
         and this_.EVENT_DATETIME<='2019-11-21 00:00:00'
         and this_.COMPANY_ID = 1;

```

The result is around 1,000,000 rows and used to take about 1.5 seconds now it takes up to 100 seconds. While the query on MySQL 5.1 uses an index on COMPANY\_ID and EVENT\_DATETIME the index is ignored on MySQL 5.7. It seems if MySQL sees that it has to parse too many rows it gives up on an index even if it would help. If I reduce the window to e.g. 10 month MySQL 5.7 uses the index again.

So on MySQL 5.1 the index COMPANY\_ID,EVENT\_DATETIME is used
on MySQL it only uses a foreign key index for COMPANY\_ID.

If I run the query without the where on the COMPANY\_ID 

```
select count(distinct this_.EVENT_ID) as y0_ from EVENT this_ 
       where this_.EVENT_DATETIME>='2018-10-22 00:00:00'
         and this_.EVENT_DATETIME<='2019-11-21 00:00:00';

```

the query is a lot faster.

Is there any way to force MySQL 5.7 to use a certain index?

If I rewrite the query to this:

```
select count(distinct this_.EVENT_ID) as y0_ from EVENT this_
     where this_.EVENT_DATETIME>='2018-10-22 00:00:00'
       and this_.EVENT_DATETIME<='2019-11-21 00:00:00'
     GROUP BY COMPANY_ID HAVING COMPANY_ID = 1;

```

it is back to about 1 to 1.5 seconds. The problem is we might have more than one of these queries and the queries are generated by Hibernate Criterias which do not support HAVING so my workaround won't work in real life.

Update: 
MySQL 5.7
Explain for 12 month query (1050757 rows in 40 seconds)

```
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "673838.60"
    },
    "table": {
      "table_name": "this_",
      "access_type": "ref",
      "possible_keys": [
      "PRIMARY",
      "FK_EVENT_COMPANY",
      "IX_REFERENCE",
      "IX_DATE_TIME",
      "EVENT_DATETIME",
      "IDX_CE_COMPANY_TYPE",
      "IDX_CE_COMPANY_DATE",
      "IDX_CE_DATE_COMPANY"
      ],
      "key": "FK_EVENT_COMPANY",
      "used_key_parts": [
        "COMPANY_ID"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 2698153,
      "rows_produced_per_join": 1135826,
      "filtered": "42.10",
      "cost_info": {
        "read_cost": "134208.00",
        "eval_cost": "227165.40",
        "prefix_cost": "673838.60",
        "data_read_per_join": "1G"
      },
      "used_columns": [
        "EVENT_ID",
        "COMPANY_ID",
        "EVENT_DATETIME"
      ],
      "attached_condition": "((`test`.`this_`.`EVENT_DATETIME` >= '2018-10-22 00:00:00') and (`test`.`this_`.`EVENT_DATETIME` <= '2019-11-21 00:00:00'))"
    }
  }
}

```

Explain for 10 month query 

```
   {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "634047.16"
    },
    "table": {
      "table_name": "this_",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY",
        "FK_EVENT_COMPANY",
        "IX_REFERENCE",
        "IX_DATE_TIME",
        "EVENT_DATETIME",
        "IDX_CE_COMPANY_TYPE",
        "IDX_CE_COMPANY_DATE",
        "IDX_CE_DATE_COMPANY"
      ],
      "key": "IDX_CE_DATE_COMPANY",
      "used_key_parts": [
        "EVENT_DATETIME"
      ],
      "key_length": "9",
      "rows_examined_per_scan": 1578860,
      "rows_produced_per_join": 789430,
      "filtered": "50.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "476161.16",
        "eval_cost": "157886.00",
        "prefix_cost": "634047.16",
        "data_read_per_join": "1G"
      },
      "used_columns": [
        "EVENT_ID",
        "COMPANY_ID",
        "EVENT_DATETIME"
      ],
      "attached_condition": "((`test`.`this_`.`COMPANY_ID` = 1) and (`test`.`this_`.`EVENT_DATETIME` >= '2019-01-22 00:00:00') and (`test`.`this_`.`EVENT_DATETIME` <= '2019-11-21 00:00:00'))"
    }
  }
}

```

Interesting is that the first 12 month (slow) query does not show COMPANY\_ID in the attached\_condition while for the second 10 month query attached\_condition has a check on COMPANY\_ID.

ANALYZE TABLE as was suggested did not change anything it seems.

Update 2:
Explain for MySQL 5.1 (does not support JSON format) takes 1.3 sec

```
1    SIMPLE         this_  range   FK_EVENT_COMPANY,IX_DATE_TIME,EVENT_DATETIME,IDX_CE_COMPANY_TYPE,IDX_CE_COMPANY_DATE    IDX_CE_COMPANY_DATE 16      NULL    2018704   Using where; Using index
```