How to find the length of multiple column index in MySQL Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command:

CREATE TABLE test_ (
  testId bigint(20) NOT NULL,
  name varchar(75) DEFAULT NULL,
  key_ varchar(200) DEFAULT NULL,
  user varchar(255) DEFAULT NULL,
  PRIMARY KEY (testId),
  UNIQUE KEY NKU (name,key_,user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
You'r lucky you have only one secondary index, the PRIMARY KEY is a clustered index so it isn't included in the index statistics.

You can find the answer with the field INDEX_LENGTH of the information_schema.tables view (result in bytes):

SELECT  INDEX_LENGTH 
FROM    information_schema.tables 
WHERE   table_schema = "myDatabase" 
AND     table_name = "test_";


I recommend you to do a ANALYZE TABLE before to get more accurate estimates:

ANALYZE TABLE myDatabase.test_;


If you have more than one secondary index in your table you should have been to drop all but one and show the INDEX_LENGHT:

CREATE TABLE myDatabase.test_ (
  testId bigint(20) NOT NULL,
  name varchar(75) DEFAULT NULL,
  key_ varchar(200) DEFAULT NULL,
  user varchar(255) DEFAULT NULL,
  PRIMARY KEY (testId),
  UNIQUE KEY NKU (name,key_,user)
  UNIQUE NKU2 (user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE myDatabase.test_ DROP INDEX NKU2;

ANALYZE TABLE myDatabase.test_;

SELECT  INDEX_LENGTH 
FROM    information_schema.tables 
WHERE   table_schema = "myDatabase" 
AND     table_name = "test_";


Max.