MySQLMyIsam storage engine index length limit test records

When creating an index, the MySQLMyIsam storage engine has a strict length limit. The maximum length of all index keys cannot exceed 1000, and it is not the sum of actual data lengths, the sum of the index key field definition length. Let's take a simple test and record it. Root @ sky :~ # Mysql-usky-p-h127.0

The MySQL MyIsam storage engine imposes a strict length limit on the index key length when creating an index. The maximum length of all index keys cannot exceed 1000, it is not the sum of the actual data length, but the sum of the index key field definition length. Let's take a simple test and record it. Root @ sky :~ # Mysql-u sky-p-h127.0

The MySQL MyIsam storage engine imposes a strict length limit on the index key length when creating an index. The maximum length of all index keys cannot exceed 1000, it is not the sum of the actual data length, but the sum of the index key field definition length. Let's take a simple test and record it.

Root @ sky :~ # Mysql-u sky-p-h127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 44
Server version: 5.0.51a-log MySQL Community Server (GPL)

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Sky@127.0.0.1: (none) 05:23:08> use test;
Database changed
Sky@127.0.0.1: test 05:23:11>
Sky@127.0.0.1: test 05:23:12>

Create a MyIsam table, select latin1 for the character set, and set the three fields to varchar 255 ,:
Sky@127.0.0.1: test 05:23:12> create table test_ind
-> (A varchar (255 ),
-> B varchar (255 ),
-> C varchar (255)
->) Engine = myisam charset = latin1;
Query OK, 0 rows affected (0.01 sec)

Effect:
Sky@127.0.0.1: test 05:23:32> show create table test_ind \ G
* *************************** 1. row ***************************
Table: test_ind
Create Table: create table 'test _ ind '(
'A' varchar (255) default NULL,
'B' varchar (255) default NULL,
'C' varchar (255) default NULL
) ENGINE = MyISAM default charset = latin1
1 row in set (0.00 sec)

Three fields should be indexed together (the length should be less than 1000)

Sky@127.0.0.1: test 05:23:41> create index test_a_ B _c_ind on test_ind (a, B, c );
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

We can see that the creation is successful.

Next we will perform a character set conversion to convert the character set to utf8

Sky@127.0.0.1: test 05:25:54> alter table test_ind convert to charset utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
Sky@127.0.0.1: test 05:26:24>
Sky@127.0.0.1: test 05:28:03> show create table test_ind \ G
* *************************** 1. row ***************************
Table: test_ind
Create Table: create table 'test _ ind '(
'A' varchar (255) default NULL,
'B' varchar (255) default NULL,
'C' varchar (255) default NULL
) ENGINE = MyISAM default charset = latin1
1 row in set (0.00 sec)

The result is an error. Check whether the table is successful. It is still the character set of latin1.

Now we drop the index and convert the character set.

Sky@127.0.0.1: test 05:28:10> drop index test_a_ B _c_ind on test_ind;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Sky@127.0.0.1: test 05:28:15> alter table test_ind convert to charset utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
Sky@127.0.0.1: test 05:28:20> show create table test_ind \ G
* *************************** 1. row ***************************
Table: test_ind
Create Table: create table 'test _ ind '(
'A' varchar (255) default NULL,
'B' varchar (255) default NULL,
'C' varchar (255) default NULL
) ENGINE = MyISAM default charset = utf8
1 row in set (0.00 sec)
The conversion was successful.

Now create an index to see how it works:

Sky@127.0.0.1: test 05:28:36> create index test_a_ B _c_ind on test_ind (a, B, c );
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Failed to reduce index key field
Sky@127.0.0.1: test 05:28:54> create index test_a_ B _c_ind on test_ind (a, B );
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Continue to decrease
Sky@127.0.0.1: test 05:29:00> create index test_a_ B _c_ind on test_ind ();
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

OK.

Finally, let's see if other storage engines have such restrictions? Let's look at Innodb, one of the most widely used storage engines:

Sky@127.0.0.1: test 05:29:03> drop index test_a_ B _c_ind on test_ind;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Sky@127.0.0.1: test 05:29:54>
Sky@127.0.0.1: test 05:29:58>
Sky@127.0.0.1: test 05:30:11> alter table test_ind engine = innodb;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
Sky@127.0.0.1: test 05:31:15> show create table test_ind \ G
* *************************** 1. row ***************************
Table: test_ind
Create Table: create table 'test _ ind '(
'A' varchar (255) default NULL,
'B' varchar (255) default NULL,
'C' varchar (255) default NULL
) ENGINE = InnoDB default charset = utf8
1 row in set (0.00 sec)

Sky@127.0.0.1: test 05:31:23> create index test_a_ B _c_ind on test_ind (a, B, c );
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

The creation is successful, and Innodb does not have this restriction.

Original article address: MySQL MyIsam storage engine index length limit test records, thanks to the original author for sharing.