Unable to add index on multiple fields in a table --INNODB

anil 0
Viewed 38
select
    *
from
    A
where
    a = 1755
    and
    b = 11
    and
    c = 50
    and
    d = 11
    and
    response != '';

create index idx_test on A (a, b, c, d, response );

While adding index got an error

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

DROP TABLE IF EXISTS A;

CREATE TABLE A (
    id       int unsigned           NOT NULL AUTO_INCREMENT,
    a        int unsigned           NOT NULL,
    b        int unsigned  DEFAULT      NULL,
    c        int unsigned           NOT NULL,
    d        int unsigned           NOT NULL,
    response varchar(5000) DEFAULT      NULL,

    PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;
performance mysql
1 Answers

The error message has told your the reason.

Also in MySQL Official Document we can find below description:

  • The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.
  • The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format.

So the reason is obivious: column response length is exceed the limit

You need to reduce the size of response

lucumt0
Related