How does MySQL use index mappings?

Question

I am wondering if MySQL considers accounting during index generation, or if the index is generated the same regardless of sorting, matching is only taken into account when the index is subsequently crawled.

For my purposes, I would like to use colulation utf8_unicode_ci in the field. I know that this particular sorting has relatively high performance, but it is still important for me to use it.

I have an index in this field that is used to satisfy an ORDER BY clause, quickly getting the rows in order (avoiding the file array). However, I’m not sure whether using this mapping will affect the speed of the rows, since they are read from the index, or if the index stores data in an already normalized state in accordance with this mapping, which reduces performance so that the index is completely generated and not read back.

Answer 1

I believe that the btree structure will be different because it should compare the column values ​​differently.

Take a look at these two query plans:

mysql> explain select * from sometable where keycol = '3';
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | pro   | ref  | PRIMARY       | PRIMARY | 66      | const |   34 | Using where; Using index | 
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+


mysql> explain select * from sometable where binary keycol = '3';
+----+-------------+-------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | pro   | index | NULL          | PRIMARY | 132     | NULL | 14417 | Using where; Using index | 
+----+-------------+-------+-------+---------------+---------+---------+------+-------+--------------------------+

, . , , , , , , , .

.

, , - ; , MySQL , .

UPDATE:

:

mysql> explain select * from sometable order by keycol collate latin1_general_cs;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra                       |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | pro   | index | NULL          | PRIMARY | 132     | NULL | 14417 | Using index; Using filesort | 
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+

mysql> explain select * from sometable order by keycol ;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | pro   | index | NULL          | PRIMARY | 132     | NULL | 14417 | Using index | 
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

"filesort", . , mysql , quicksort , , . , , mysql .

Answer 2

MySQL . , utf8_unicode_ci, utf8_unicode_ci.

, 100% , .

, , .