Hints to use indexes, skip indexes, force indexes in MySQL

MySQL commands for use Index hints in SQL Query like USE INDEX, IGNORE INDEX, FORCE INDEX

USE INDEX hint is used only one of the named indexes to find rows in the table.
IGNORE INDEX hint means not using some particular index or indexes during query execution.
FORCE INDEX hint to force the index to use in the Query execution plan.

Example of using the indexes with hints:

Use MySQL without using the Hints:

MariaDB [classicmodels]> create index city_idx on offices(city);
Query OK, 0 rows affected (0.113 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [classicmodels]> create index city_count_idx on offices(city,country);
Query OK, 0 rows affected (0.060 sec)
Records: 0  Duplicates: 0  Warnings: 0


MariaDB [classicmodels]> explain select * from offices where city = 'a'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: offices
         type: ref
possible_keys: city_idx,city_count_idx
          key: city_idx
      key_len: 52
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.001 sec)

ERROR: No query specified

USE INDEX hint:

MariaDB [classicmodels]> explain select * from offices use index (city_count_idx) where city = 'a'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: offices
         type: ref
possible_keys: city_count_idx
          key: city_count_idx
      key_len: 52
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.000 sec)

ERROR: No query specified

IGNORE INDEX hint:

MariaDB [classicmodels]> explain select * from offices IGNORE index (city_count_idx,city_idx) where city = 'a'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: offices
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra: Using where
1 row in set (0.000 sec)

ERROR: No query specified

FORCE INDEX hint:

MariaDB [classicmodels]> explain select * from offices force index (city_count_idx) where city = 'a'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: offices
         type: ref
possible_keys: city_count_idx
          key: city_count_idx
      key_len: 52
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.001 sec)

ERROR: No query specified