Is LIKE 'xxx%' equivalent to range query? How do LIKE 'xxx%' use index?

Question

Some one says, (1)LIKE 'xxx%' is equivalent to range query; (2)columns after the range column cannot use the index. But, I find the two statement are contradictory in an example which will be provided below. So, I want to know what the exact process of querying index tree when we use LIKE 'xxx%'.

I used the employees.titles table of MySQL document.

Here is the table structure.

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no    | int(11)     | NO   | PRI | NULL    |       |
| title     | varchar(50) | NO   | PRI | NULL    |       |
| from_date | date        | NO   | PRI | NULL    |       |
| to_date   | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

Here is the index structure.

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| titles |          0 | PRIMARY  |            1 | emp_no      | A         |      300698 |     NULL | NULL   |      | BTREE      |         |               |
| titles |          0 | PRIMARY  |            2 | title       | A         |      441654 |     NULL | NULL   |      | BTREE      |         |               |
| titles |          0 | PRIMARY  |            3 | from_date   | A         |      441654 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Here is the query statement.

mysql> EXPLAIN SELECT * FROM employees.titles 
               WHERE emp_no='10001' 
                     AND title LIKE 'Senior%' 
                     AND from_date='1986-06-26';
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | titles | NULL       | range | PRIMARY       | PRIMARY | 59      | NULL |    1 |    10.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

If title "LIKE 'Senior%'" is equivalent to "title >=Senior and title < Senios", why the key_len is 59, which means all columns of the primary key are used?

The result of EXPLAIN format=JSON

mysql> EXPLAIN format=json SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%' AND from_date='1986-06-26'\G;
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.29"
    },
    "table": {
      "table_name": "titles",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "emp_no",
        "title"
      ],
      "key_length": "59",
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 0,
      "filtered": "10.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "0.01",
        "prefix_cost": "1.29",
        "data_read_per_join": "3"
      },
      "used_columns": [
        "emp_no",
        "title",
        "from_date",
        "to_date"
      ],
      "attached_condition": "((`employees`.`titles`.`from_date` = '1986-06-26') and (`employees`.`titles`.`emp_no` = '10001') and (`employees`.`titles`.`title` like 'Senior%'))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)

Answer 1

Ordering of column in an index is very important for a particular WHERE clause. In your current Primary Key, the order is emp_no -> title -> from_date. When using AND condition between various columns, MySQL will continue using the columns in the Composite index equating to a constant value, until it encounters the Range condition.

Now, note that LIKE 'Senior% is basically a Range condition, because there are various possibilities in title with Senior as prefix. Because, MySQL encountered the Range condition at the second column in the index, it does not use the third column in index, i.e., from_date (`from_date='1986-06-26').

You can confirm this from the Extra column in your EXPLAIN result. It says Using Where there. It basically means that after doing the Index Lookup (using the first two columns), it goes into Data Tree, and gets the from_date value to filter out based on your WHERE condition (`from_date='1986-06-26').

If you want all the columns in the index to be used, you will need to change the ordering of columns in your Primary Key to the following, or define a new index:

(emp_no, from_date, title)

Depending on your MySQL version, you can get much more details, if you run EXPLAIN format=JSON. Especially, look at the used columns part in the JSON result from the Explain. Another way to check is if you define the new indexes (as specified above), and re-run the query, you will notice that Using Where has gone away from the Explain result now.

Edit

Thanks for adding the EXPLAIN format=JSON result. You can clearly see used_key_parts value in it:

"used_key_parts": [
        "emp_no",
        "title"
      ]

It clearly indicates that only first two columns in the Primary Key are used.