Understanding the REF column in MySQL EXPLAIN

Question

I have few questions about the REF column in the EXPLAIN output. MySQL Version 5.7.31 Community Server.

The table structure

| table1 | CREATE TABLE `table1` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `cp_id` bigint(20) NOT NULL,
 `ms_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `bu_id` int(8) DEFAULT NULL,
 `create_time` datetime DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 `status` int(8) DEFAULT NULL,
 `sync_time` date NOT NULL DEFAULT '1970-01-01',
 PRIMARY KEY (`id`),
 KEY `idx_update_time` (`update_time`),
 KEY `idx_ms_st_sy_cp` (`ms_id`,`status`,`sync_time`,`cp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=40264018 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
explain select cp_id, max(sync_time) as sync_time from table1 where ms_id = '678645347824' and status = 1 and sync_time >= '2024-02-09' group by cp_id;
+----+-------------+--------------------+------------+-------+-------------------------------------------------------------------+-----------------------------------------+---
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------+------------+-------+-------------------------------------------------------------------+-----------------------------------------+---
| 1 | SIMPLE | table1 | NULL | range | idx_ms_st_sy_cp | idx_ms_st_sy_cp | 107 | NULL | 900 | 100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------------------+------------+-------+-------------------------------------------------------------------+-----------------------------------------+---
1 row in set, 1 warning (0.00 sec)

In the first EXPLAIN plan, the ref column is NULL.

On this Link https://www.linkedin.com/pulse/mysql-explain-explained-gowrav-vishwakarma-%E0%A4%97-%E0%A4%B0%E0%A4%B5-%E0%A4%B5-%E0%A4%B6-%E0%A4%B5%E0%A4%95%E0%A4%B0-%E0%A4%AE-/

it says if the ref column is NULL, meaning index is not being used. On MySQL documentation also it says the same thing

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_ref

1. What exactly the index is doing here?

2. Is it only being used as a covering index? Using Index in the EXTRA column tells me that the index is being used as a Covering Index. (please correct me if I wrong.)

3. The key_len 107 tells me that all the three columns (ms_id, status, sync_time) are being used. As various sources explain the key_len can be determined to check which columns are being used. So key_len of 107 (ms_id, status, sync_time) = (99 + 5 + 3). This 107 key_len can be calcuated from the below EXPLAINs. If the key_len tells me that the index is being used they why the REF column is NULL?

4. Why the TYPE is RANGE? Does this RANGE table access mean that only the sync_time column is being used? Can I conclude so? key_len = 107 and TYPE=RANGE it's bit confusing.

5. Are the columns TYPE, KEY and REF related to each other. I read that the columns KEY and REF are related and REF shows the kind of index access. and probably it is somehow connected.

   explain select cp_id, max(sync_time) as sync_time from table1 where ms_id = '678645347824' group by cp_id; +----+-------------+--------------------+------------+------+-------------------------------------------------------------------+-----------------------------------------+---- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------------+------------+------+-------------------------------------------------------------------+-----------------------------------------+---- | 1 | SIMPLE | table1 | NULL | ref | idx_ms_st_sy_cp | idx_ms_st_sy_cp | 99 | const | 905 | 100.00 | Using where; Using index; Using temporary; Using | filesort | +----+-------------+--------------------+------------+------+-------------------------------------------------------------------+-----------------------------------------+---- 1 row in set, 1 warning (0.00 sec)

In the second EXPLAIN, where I use only ms_id, then TYPE is REF and the REF column is CONST meaning that it is using the Index using the equality operator and key_len is 99 for the ms_id.

explain select cp_id, max(sync_time) as sync_time from table1 where ms_id = '678645347824' and status = 1 group by cp_id;
+----+-------------+--------------------+------------+------+-------------------------------------------------------------------+-----------------------------------------+----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------+------------+------+-------------------------------------------------------------------+-----------------------------------------+----
| 1 | SIMPLE | table1 | NULL | ref | idx_ms_st_sy_cp | idx_ms_st_sy_cp | 104 | const,const | 905 | 100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------------------+------------+------+-------------------------------------------------------------------+-----------------------------------------+----
1 row in set, 1 warning (0.01 sec)

In the third EXPLAIN, the TYPE is REF and the REF column is CONST,CONST because of the two = operators in the WHERE clause ms_id and status.

explain select cp_id, max(sync_time) as sync_time from table1 where ms_id = '678645347824' and status = 1 and sync_time >= '2024-02-09' and cp_id = '1' group by cp_id
;
+----+-------------+--------------------+------------+------+-------------------------------------------------------------------+---------+---------+-------------------+------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------+------------+------+-------------------------------------------------------------------+---------+---------+-------------------+------
| 1 | SIMPLE | table1 | NULL | ref | idx_ms_st_sy_cp | idx_com | 112 | const,const,const | 1 | 33.33 | Using index condition; Using where |
+----+-------------+--------------------+------------+------+-------------------------------------------------------------------+---------+---------+-------------------+------
1 row in set, 1 warning (0.00 sec)

In the last EXPLAIN again, it gets even wonderful, the REF column shows CONST,CONST,CONST meaning that the sync_time column is not being used. Am I correct?

Answer 1

ref usually mentions the columns coming from a JOIN or, in your case, simply "constants". NULL usually means that it is not using any index. In your case, "Using index" means that the index is "covering", but the columns may not be in the optimal order.

I wonder if this would run faster:

select cp_id, max(sync_time) as max_st
 from table1
 where ms_id = '678645347824'
 and status = 1
 group by cp_id;
 HAVING max_st >= '2024-02-09'

together with a composite index with the columns in this order:

INDEX(ms_id, status, cp_id, sync_time)

• Avinash Pawar: Thank you for response @Rick James, understood. I will definitely check the query shared. So I can conclude that the index I have is not being used for narrowing down the result but being used a covering index only. Also just for the sake of understanding, why the key_len is 107 if the index is not being used to narrow down results? The key_len 107 is for the first three columns in my index (ms_id, status, sync_time) or it will print key_len even if it is using the index as covering index only.
• Rick James: @AvinashPawar - The first example confuses me -- It has a key_len implying (as you say) the use of 3 columns for filtering. But it does not have "const,const", which would more strongly imply narrowing down. Your 3rd query does not say "Using index", implying that it is slowed down by not being "covering". ("Using index condition" is something else.)