Description:
When creating an index on the prefix of a UTF-8 column, the prefix chosen by MySQL is much larger than specified.
The documentation says:
"For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax to index a prefix consisting of the first length characters of each column value."
According to this statement, the following query should generate an index on the first 64 _characters_ of the foobar column:
CREATE INDEX foobar ON foo (foobar(64));
Regarding SHOW INDEX, the documentation says:
"Sub_part: The number of indexed characters if the column is only partly indexed. NULL if the entire column is indexed."
But, assuming foobar is a utf8 column, SHOW INDEX reports, "Sub_part: 192". According to the documentation, this would mean that the prefix used has a length of 192 characters which is not true. Maybe MySQL indexes the first 192 bytes to ensure to have the first 64 characters included in the prefix, but this is not what should be reported here.
Either the documentation is wrong or this is a bug of the MySQL server.
How to repeat:
mysql> CREATE TABLE foo (foobar VARCHAR(255)) CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE INDEX foobar ON foo (foobar(64));
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM foo\G
*************************** 1. row ***************************
Table: foo
Non_unique: 1
Key_name: foobar
Seq_in_index: 1
Column_name: foobar
Collation: A
Cardinality: NULL
Sub_part: 192
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
1 row in set (0.00 sec)
Description: When creating an index on the prefix of a UTF-8 column, the prefix chosen by MySQL is much larger than specified. The documentation says: "For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax to index a prefix consisting of the first length characters of each column value." According to this statement, the following query should generate an index on the first 64 _characters_ of the foobar column: CREATE INDEX foobar ON foo (foobar(64)); Regarding SHOW INDEX, the documentation says: "Sub_part: The number of indexed characters if the column is only partly indexed. NULL if the entire column is indexed." But, assuming foobar is a utf8 column, SHOW INDEX reports, "Sub_part: 192". According to the documentation, this would mean that the prefix used has a length of 192 characters which is not true. Maybe MySQL indexes the first 192 bytes to ensure to have the first 64 characters included in the prefix, but this is not what should be reported here. Either the documentation is wrong or this is a bug of the MySQL server. How to repeat: mysql> CREATE TABLE foo (foobar VARCHAR(255)) CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) mysql> CREATE INDEX foobar ON foo (foobar(64)); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW INDEX FROM foo\G *************************** 1. row *************************** Table: foo Non_unique: 1 Key_name: foobar Seq_in_index: 1 Column_name: foobar Collation: A Cardinality: NULL Sub_part: 192 Packed: NULL Null: YES Index_type: BTREE Comment: 1 row in set (0.00 sec)