O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required