Will an index be used in a query that includes two column in the where clause with only the first co-CodePudding

I read that

if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3)

I'm currently indexing my database tables but I want to know if my query for example contains col1, col2 and col5 in the where clause, will the database still use the index created (col1, col2, col3) even though col5 is not part of the index created?

E.g

SELECT * FROM my_table WHERE col1='male' AND col2='24' AND col5='teacher'

CodePudding user response：

Yes, MySQL can use a subset of an index, as long as the columns are the left-most subset.

Demo:

mysql> create table my_table (col1 varchar(10), col2 varchar(10), col3 varchar(10), col4 varchar(10), col5 varchar(10), key(col1, col2, col3));

mysql> explain SELECT * FROM my_table WHERE col1='male' AND col2='24' AND col5='teacher'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_table
   partitions: NULL
         type: ref
possible_keys: col1
          key: col1
      key_len: 66
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using where

EXPLAIN shows you a report of how the optimizer plans to use indexes. In this case, notice key: col1 which shows which index is chosen, and ref: const,const which indicates it will compare two constant values in the query to columns in the index.

If I search on a subset of columns that are not a contiguous left-most columns of the index, it cannot use more than one column of the index. See ref: const below, indicating only one value is used to do the lookup.

mysql> explain SELECT * FROM my_table WHERE col1='male' AND col3='24' AND col5='teacher'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_table
   partitions: NULL
         type: ref
possible_keys: col1
          key: col1
      key_len: 33
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where

If I search for a contiguous subset of columns, but they are not the left-most columns of the index, it can't use the index at all. The example below shows that it must resort to a table-scan, indicated by type: ALL.

mysql> explain SELECT * FROM my_table WHERE col2='male' AND col3='24' AND col5='teacher'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_table
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

CodePudding user response：

Yes. Any index starting with (col1, col2) will be fully used for that query. But, if you change it to, say WHERE col1 > 5, it will use only col1 of the index. And there are many other exceptions. So, please provide "real" queries for analysis.

"Using index" means that all the columns needed anywhere in the SELECT were found (not necessarily in the optimal order) in the chosen INDEX. It is called a "covering" index. "Using where" does not provide much info.

The order of AND'd items in WHERE does not matter. The order in the INDEX does matter.

More discussion: Index Cookbook