Understanding the REF column in MySQL EXPLAIN

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?