How I can increase max key length to more than 767 bytes?

Asked by NT Man on 2014-01-03

CREATE TABLE `clients` (
  `id_client` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `fullname` VARCHAR(700) DEFAULT NULL,
  PRIMARY KEY (`id_client`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

ALTER TABLE `clients`
  ADD INDEX `fullname` (`fullname`(700));

Warning Code : 1071
Specified key was too long; max key length is 767 bytes

If I cannot do it with database setting can I fill bug report?
MariaDB 10.0.7

Question information

Language:
English Edit question
Status:
Answered
For:
MariaDB Edit question
Assignee:
No assignee Edit question
Last query:
2014-01-03
Last reply:
2014-01-03
Axel Schwenke (ahel) said : #1

This is not a bug, but a (rather well known) limitation in MySQL/MariaDB.

From the manual at http://dev.mysql.com/doc/refman/5.6/en/create-table.html

"For CHAR, VARCHAR, BINARY, and VARBINARY columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length.
...
Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters ..."

A utf8 character can use up to 3 bytes. Hence you cannot index columns or prefixes of columns longer than 333 (MyISAM) or 255 (InnoDB) utf8 characters.

Side note: 700 characters is a lot, about the size of this answer. You probably could reduce the row length from 700 to i.e. 255?

NT Man (mikhail-v-gavrilov) said : #2

Ok, I found this value of length for utf-8 experimentally.
But what I must do if I really needed much longer varchar fields??? Can I fill feature request for MariaDB?

Elena Stepanova (elenst) said : #3

It has been implemented already in InnoDB 5.6 and merged into MariaDB 10.0. The variable is called innodb_large_prefix, and it works with newer InnoDB file formats and row formats:

MariaDB [test]> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> set global innodb_file_format=on;
ERROR 1231 (42000): Variable 'innodb_file_format' can't be set to the value of 'ON'
MariaDB [test]> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> CREATE TABLE `clients` (
    -> `id_client` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `fullname` VARCHAR(700) DEFAULT NULL,
    -> PRIMARY KEY (`id_client`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> ALTER TABLE `clients`
    -> ADD INDEX `fullname` (`fullname`(700));
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> SHOW CREATE TABLE clients;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| clients | CREATE TABLE `clients` (
  `id_client` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fullname` varchar(700) DEFAULT NULL,
  PRIMARY KEY (`id_client`),
  KEY `fullname` (`fullname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT @@version;
+----------------------+
| @@version |
+----------------------+
| 10.0.7-MariaDB-debug |
+----------------------+
1 row in set (0.01 sec)

NT Man (mikhail-v-gavrilov) said : #4

Elena, thanks for answer.

I tested this solution and it works on MariaDB 10.0.7

But on MariaDB 5.5.34 index won't works :(

    id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ---------- ------ ------------- ------ ------- ------ ------ ----------------
     1 SIMPLE crm_client ALL (NULL) (NULL) (NULL) (NULL) 793 Using filesort

CREATE TABLE `crm_client` (
  `id_client` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `SNAME` VARCHAR(700) DEFAULT NULL,
  `FNAME` VARCHAR(700) DEFAULT NULL,
  `client_type` INT(10) NOT NULL,
  `INN` VARCHAR(50) DEFAULT NULL,
  `res` ENUM('R','N') DEFAULT NULL,
  `emp_id_first` INT(10) DEFAULT NULL,
  `fdate` DATE DEFAULT NULL,
  `mdate` TIMESTAMP NULL DEFAULT NULL,
  `udate` TIMESTAMP NULL DEFAULT NULL,
  `comment` LONGTEXT,
  `terrorist` INT(10) DEFAULT NULL,
  `risk_type` ENUM('normal','high','critical') DEFAULT NULL,
  `risk_str` ENUM('normal','high','critical') DEFAULT NULL,
  `risk_oper` ENUM('normal','high','critical') DEFAULT NULL,
  `risk_mark` LONGTEXT,
  `ank_period` INT(10) DEFAULT NULL,
  `emp_id` INT(10) DEFAULT NULL,
  `resCntr` VARCHAR(2) DEFAULT NULL,
  `foreign_public` INT(10) DEFAULT NULL,
  `promotion` TINYINT(1) DEFAULT NULL,
  `contr_agent` INT(10) DEFAULT NULL,
  `id_sale` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id_client`),
  KEY `INN` (`INN`),
  KEY `crm_client_ibfk_1` (`client_type`),
  KEY `FNAME` (`FNAME`),
  CONSTRAINT `crm_client_ibfk_1` FOREIGN KEY (`client_type`) REFERENCES `crm_client_type` (`client_type`) ON DELETE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=211854 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

On MariaDB 10.0.7

    id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ---------- ------ ------------- ------ ------- ------ ------ --------
     1 SIMPLE crm_client index (NULL) FNAME 768 (NULL) 9

NT Man (mikhail-v-gavrilov) said : #5

EXPLAIN SELECT
  *
FROM
  crm_client
  ORDER BY FNAME DESC
LIMIT 9

Elena Stepanova (elenst) said : #6

>> But on MariaDB 5.5.34 index won't works

It was only implemented in MySQL 5.6, hence only appears in MariaDB 10.0.
I think it's far too big a change to make it in a post-GA version such as 5.5.

montie (vlabots) said : #7

Elena Stepanova (elenst), thanks a lot, it worked for me, but i also put a global variable

 SET GLOBAL innodb_default_row_format=DYNAMIC;

This variable works on MariaDB only from >= 10.1 version, so I upgraded it to the 10.2 version.

Can you help with this problem?

Provide an answer of your own, or ask NT Man for more information if necessary.

To post a message you must log in.