Mysql – Why cp1256 collation is not 1 byte in MySQL

collationMySQL

I've read an article that cp1256 collation takes 1 byte for Arabic characters, but when I created an index on the column that was cp1256 with type of varchar(900), only 767 characters picked up for index purposes:

CREATE TABLE col (
id int(11) NOT NULL AUTO_INCREMENT,
arabic varchar(900) CHARACTER SET cp1256 NOT NULL,
PRIMARY KEY (id),
KEY (arabic(767)
) ENGINE=InnoDB;

Arabic varchar(900) truncated to (767) for my index.
Why is that the case?

Best Answer

That's a limit of the InnoDB engine. It cannot index more than 767 bytes (for a single column)

See the manual:

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

By default, an index key for a single-column index can be up to 767 bytes
...
When you attempt to specify an index prefix length longer than allowed, the length is silently reduced to the maximum length

Related Question