why prefix index is slower than index in mysql?

Question

table:(quantity：2100W)

CREATE TABLE `prefix` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  `string` varchar(750) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_string_prefix10` (`string`(10)),
  KEY `idx_string` (`string`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

discrimination:

select count(distinct(left(string,10)))/count(*) from prefix;
+-------------------------------------------+
| count(distinct(left(string,10)))/count(*) |
+-------------------------------------------+
|                                    0.9999 |
+-------------------------------------------+

result:

select sql_no_cache count(*) from prefix force index(idx_string_prefix10) 
where string <"1505d28b"
243.96s，241.88s

select sql_no_cache count(*) from prefix force index(idx_string) 
where string < "1505d28b"
7.96s，7.21s，7.53s

why prefix index is slower than index in mysql?（forgive my broken English）

explain select sql_no_cache count(*) from prefix force index(idx_string_prefix10) 
where string < "1505d28b";

+----+-------------+--------+------------+-------+---------------------+---------------------+---------+------+---------+----------+-------------+
| id | select_type |  table | partitions |  type |       possible_keys |                 key | key_len |  ref |    rows | filtered |       Extra |
+----+-------------+--------+------------+-------+---------------------+---------------------+---------+------+---------+----------+-------------+
|  1 |      SIMPLE | prefix |       NULL | range | idx_string_prefix10 | idx_string_prefix10 |      42 | NULL | 3489704 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------------+---------------------+---------+------+---------+----------+-------------+

Answer 1

When you use a prefix index, MySQL has to read from the index and also after reading the index, it has to read the row of data too, to make sure the value is selected by the WHERE condition. That's two reads, and scanning a lot more data.

When you use a non-prefix index, MySQL can read the whole string value from the index, and it knows immediately whether the value is selected by the condition, or if it can be skipped.