{"id":6883,"date":"2018-08-19T06:52:29","date_gmt":"2018-08-19T13:52:29","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6883"},"modified":"2024-01-05T03:16:40","modified_gmt":"2024-01-05T10:16:40","slug":"mysql-use-index","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-index\/mysql-use-index\/","title":{"rendered":"MySQL USE INDEX Hint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL USE INDEX<\/code> hint to instruct the query optimizer to use only a list of named indexes for a query.<\/p>\n\n\n\n

Introduction to MySQL USE INDEX hint<\/h2>\n\n\n\n

In MySQL, when you submit an SQL query to the database server, the query optimizer attempts to create an optimal query execution plan.<\/p>\n\n\n\n

To determine the best possible plan, the query optimizer relies on several parameters. One of the most crucial parameters for selecting the appropriate index is stored key distribution<\/strong>, also known as cardinality<\/a><\/strong>.<\/p>\n\n\n\n

The cardinality, however, may be not accurate. For example, if the table has been modified heavily with many inserts or deletes, the cardinality is not updated timely.<\/p>\n\n\n\n

To address this issue, you should run the ANALYZE TABLE<\/code> statement periodically to update the cardinality.<\/p>\n\n\n\n

In addition, MySQL allows you to recommend the indexes that the query optimizer should consider using an index hint.<\/p>\n\n\n\n

Here’s the basic syntax for using the USE INDEX<\/code> hint:<\/p>\n\n\n

SELECT<\/span> select_list\nFROM<\/span> table_name USE<\/span> INDEX<\/span>(index_list)\nWHERE<\/span> condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the USE INDEX<\/code> instructs the query optimizer to use one of the named indexes to find rows in the table.<\/p>\n\n\n\n
Notice that when you recommend the indexes to use, the query\u00a0optimizer may decide to use them or not depending on the query plan that it comes up with.<\/p>\n\n\n\n
MySQL USE INDEX example<\/h2>\n\n\n\n
We will use the customers <\/span><\/span>table from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
First, use the SHOW INDEXES<\/a><\/code> statement to display all indexes of the  customers<\/code> table:<\/p>\n\n\n
SHOW<\/span> INDEXES<\/span> FROM<\/span> customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Second, create four indexes as follows:<\/p>\n\n\n
CREATE<\/span> INDEX<\/span> idx_c_ln  ON<\/span> customers(contactLastName);\nCREATE<\/span> INDEX<\/span> idx_c_fn ON<\/span> customers(contactFirstName);\nCREATE<\/span> INDEX<\/span> idx_name_fl  ON<\/span> customers(contactFirstName,contactLastName);\nCREATE<\/span> INDEX<\/span> idx_name_lf  ON<\/span> customers(contactLastName,contactFirstName);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, find customers whose contact first name or contact last name starts with the letter A. Use the EXPLAIN<\/code> statement check which indexes are used:<\/p>\n\n\n
EXPLAIN<\/span> SELECT<\/span> *\nFROM<\/span>\n    customers\nWHERE<\/span>\n    contactFirstName LIKE<\/span> 'A%'<\/span>\n        OR<\/span> contactLastName LIKE<\/span> 'A%'<\/span>\\G<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following shows the output of the statement:<\/p>\n\n\n
           id: 1\n  select_type: SIMPLE\n        table: customers\n   partitions: NULL\n         type: index_merge\npossible_keys: idx_c_ln,idx_c_fn,idx_name_fl,idx_name_lf\n          key: idx_c_fn,idx_c_ln\n      key_len: 52,52\n          ref: NULL\n         rows: 16\n     filtered: 100.00\n        Extra: Using sort_union(idx_c_fn,idx_c_ln); Using where\n1 row in set<\/span>, 1<\/span> warning<\/span> (0.00<\/span> sec)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the query optimizer used the idx_c_fn<\/code> and idx_c_ln<\/code> indexes.<\/p>\n\n\n\n
Fourth, if you think that it is better to use the idx_c_fl<\/code> and idx_c_lf<\/code> indexes, you use the USE INDEX<\/code> clause as follows:<\/p>\n\n\n
EXPLAIN<\/span> SELECT<\/span> *\nFROM<\/span>\n    customers\nUSE<\/span> INDEX<\/span> (idx_name_fl, idx_name_lf)\nWHERE<\/span>\n    contactFirstName LIKE<\/span> 'A%'<\/span>\n        OR<\/span> contactLastName LIKE<\/span> 'A%'<\/span>\\G<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that this is just for demonstration purposes, not the best choice though.<\/p>\n\n\n\n
The following illustrates the output:<\/p>\n\n\n
           id: 1\n  select_type: SIMPLE\n        table: customers\n   partitions: NULL\n         type: index_merge\npossible_keys: idx_name_fl,idx_name_lf\n          key: idx_name_fl,idx_name_lf\n      key_len: 52,52\n          ref: NULL\n         rows: 16\n     filtered: 100.00\n        Extra: Using sort_union(idx_name_fl,idx_name_lf); Using where\n1 row in set<\/span>, 1<\/span> warning<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
These are the changes:<\/p>\n\n\n\n
    \n
  • The possible_keys<\/code> column only lists the indexes specified in the USE INDEX<\/code> clause.<\/li>\n\n\n\n
  • The key column has both idx_name_fl<\/code> and idx_name_lf<\/code>. It means that the query optimizer used the recommended indexes instead.<\/li>\n<\/ul>\n\n\n\n
    The USE INDEX<\/code> can be useful if the query optimizer uses the wrong index from the list of possible indexes.<\/p>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the MySQL USE INDEX<\/code> hint to instruct the query optimizer to use the only list of specified indexes.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t