MySQL create index error

Question

Issue is to create index for table 'visits_visit' (Django visit app), because every query lasts at least 60 ms and is going to be worse.

CREATE INDEX resource ON visits_visit (object_app(200), object_model(200), object_id(200));

It returns:

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

What to do? Structure of table is on the screenshot.

Possible duplicate of [#1071 - Specified key was too long; max key length is 1000 bytes](http://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes) — Pathik Vejani, Feb 11 '16 at 11:28
MySQL has limit on index Key. One of your CharField that is indexed is too long — Aviah Laor, Feb 11 '16 at 11:31
"Prefix" indexing is virtually useless, especially in the composite index you suggested. — Rick James, Feb 13 '16 at 22:07
Please provide `SHOW CREATE TABLE`; it is more descriptive than `DESCRIBE`. — Rick James, Feb 13 '16 at 22:16

score 1 · Accepted Answer · edited May 23 '17 at 12:17

See the reference to a possible duplicate question already answered in comments under your question. Or should I say a canonical duplicate target to close this question to if it does close. That said, not much there in that reference in terms of storage engines or character sets.

In your case the character set factors in with the use of string-type columns in your composite index.

A side note is certainly performance. Don't expect a great one in general with what you are attempting. Your index is way too wide and may very well not even be of the intended use. Indexes and their benefit need careful scrutiny. This can be ascertained with the use of mysql explain. See the following, in particular the General Comments section.

Please see the following article Using Innodb_large_prefix to Avoid ERROR 1071 and below is an excerpt.

The character limit depends on the character set you use. For example if you use latin1 then the largest column you can index is varchar(767), but if you use utf8 then the limit is varchar(255). There is also a separate 3072 byte limit per index. The 767 byte limit is per column, so you can include multiple columns (each 767 bytes or smaller) up to 3072 total bytes per index, but no column longer than 767 bytes. (MyISAM is a little different. It has a 1000 byte index length limit, but no separate column length limit within that). One workaround for these limits is to only index a prefix of the longer columns, but what if you want to index more than 767 bytes of a column in InnoDB? In that case you should consider using innodb_large_prefix, which was introduced in MySQL 5.5.14 and allows you to include columns up to 3072 bytes long in InnoDB indexes. It does not affect the index limit, which is still 3072 bytes.

Also see the Min and Max section from the Mysql Manual Page Limits on InnoDB Tables

score -1 · Answer 2 · answered Feb 13 '16 at 22:16

The 'right' answer is to shorten the fields and/or normalize them.

Do you really have 200-character-long apps, models, etc? If not, shorten the fields.

Probably model is repeated in the table a lot? If so, normalize it and replace the column with the id from normalizing it.

You seem to be using MyISAM; you could (should) also switch to InnoDB. That will change the error message, or it might make it go away.

Are you using utf8 characters? Are you doing everything in English? Changing the CHARACTER SET could make 200 characters mean 200 bytes, not 600 (utf8) or 800 (utf8mb4).

Changing the character set for ip_address would shrink its footprint from 15 * (bytes/char). So would changing from CHAR to VARCHAR. Note also that 15 is insufficient to handle IPv6.

MySQL create index error

2 Answers2