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*************************** ...
