CODEWITHSUNDEEP
mysqlsqldatabaseindexingsql-tuning
user1342336
user1342336

Reputation: 977

how does mysql allocate the key length of a primary key or foreign key?

I created a table with 2 different charset type: utf8, and latin1.

1)

CREATE TABLE `aaa` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2)

CREATE TABLE `aaa` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then I insert value into several rows. When using 'explain' to select both tables, I got different key_len:

1)

mysql> explain select count(*) from aaa where name = "haha";

| id | select_type | table | type | possible_keys | key | key_len | ref | row s | Extra |

| 1 | SIMPLE | aaa | ref | name | name | 258 | const | 2 | Using where; Using index |

1 row in set (0.00 sec)

2)

mysql> explain select count(*) from aaa where name = "haha";

| id | select_type | table | type | possible_keys | key | key_len | ref | row s | Extra |

| 1 | SIMPLE | aaa | ref | name | name | 768 | const | 2 | Using where; Using index |

1 row in set (0.01 sec)

I don't know how does the system allocate the key_len for the similar tables which were defined using different charset?

Upvotes: 0

Views: 704

Answers (1)

Markus Winand
Markus Winand

Reputation: 8706

As utf8 is a multi-byte character set, MySQL needs to reserve three bytes for each character:

utf8, a UTF-8 encoding of the Unicode character set using one to three bytes per character.

http://dev.mysql.com/doc/refman/5.6/en/charset-unicode.html

Besides that, there seems to be three bytes of overhead.

Upvotes: 2

Related Questions