-1
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;
Dai
  • 141,631
  • 28
  • 261
  • 374
anil
  • 1
  • 1
  • 1
    Please post your **actual** `CREATE TABLE` statement instead of describing it. – Dai Sep 11 '22 at 06:41
  • 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; – anil Sep 11 '22 at 07:05
  • yeah but my actual one is-- create index idx_test on A(a,b,c,d,response); – anil Sep 11 '22 at 07:32
  • We can't help you if you aren't giving us accurate details in your original posting. – Dai Sep 11 '22 at 07:33
  • Ok Dai plz give a solution for that query – anil Sep 11 '22 at 07:36
  • We've already told you what the problem is in @lucumt's answer: you simply cannot create an `INDEX` over a set of columns that exceeds `3072` bytes, but your `response` column is `5000` bytes. There are alternative approaches you could use, but it would take me a long time to go through them all with no gurantee that you'd pay heed at all. I don't want to waste my life posting ignored advice to SO. – Dai Sep 11 '22 at 07:39
  • Just do `INDEX(a,b,c,d)` (without `response`). `response` won't be useful any way because of "!=". – Rick James Sep 11 '22 at 17:25
  • Ok Rick james Thank you – anil Sep 13 '22 at 07:47

1 Answers1

1

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

flyingfox
  • 13,414
  • 3
  • 24
  • 39
  • is there any possibility to add index on those fields without any data type size changes or how to optimize the above select query – anil Sep 11 '22 at 07:08
  • In my opinion,if you want to add index to such a big length variable,then `Elasticsearch` might be more suitable for you.Create index on big length column on mysql does not have meaningful usage – flyingfox Sep 11 '22 at 07:11