MySQL 8 Cookbook

How to do it...

Take the same query where you evaluated the use of the redundant index as an example; it is using intersect(from_date,from_date_2). By passing the optimizer hint (/*+ NO_INDEX_MERGE(s from_date,from_date_2) */), you avoided the use of intersect. You can achieve the same behavior by hinting the optimizer to ignore the from_date_2 index:

mysql> EXPLAIN SELECT e.emp_no, salary FROM salaries s IGNORE INDEX(from_date_2) JOIN employees e ON s.emp_no=e.emp_no WHERE from_date='2001-05-23'\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: s   partitions: NULL         type: refpossible_keys: PRIMARY,from_date          key: from_date      key_len: 3          ref: const         rows: 90     filtered: 100.00        Extra: NULL*************************** ...