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

I've been trying to get this old .sql imported into:

Server version: 10.8.3-MariaDB
Server charset: UTF-8 Unicode (utf8mb4)
InnoDB

and have read a ton of SO answers on this error, but it seems no matter how low I set the varchars, I just keep getting the same error. Originally it was:

CREATE TABLE nuke_cpg_pictures (
   pid int(11) NOT NULL auto_increment,
   aid int(11) DEFAULT '0' NOT NULL,
   filepath varchar(255) NOT NULL,
   filename varchar(255) NOT NULL,
   filesize int(11) DEFAULT '0' NOT NULL,
   total_filesize int(11) DEFAULT '0' NOT NULL,
   pwidth smallint(6) DEFAULT '0' NOT NULL,
   pheight smallint(6) DEFAULT '0' NOT NULL,
   hits int(10) DEFAULT '0' NOT NULL,
   mtime timestamp(14),
   ctime int(11) DEFAULT '0' NOT NULL,
   owner_id int(11) DEFAULT '0' NOT NULL,
   owner_name varchar(40) NOT NULL,
   pic_rating int(11) DEFAULT '0' NOT NULL,
   votes int(11) DEFAULT '0' NOT NULL,
   title varchar(255) NOT NULL,
   caption text NOT NULL,
   keywords varchar(255) NOT NULL,
   approved enum('YES','NO') DEFAULT 'NO' NOT NULL,
   user1 varchar(255) NOT NULL,
   user2 varchar(255) NOT NULL,
   user3 varchar(255) NOT NULL,
   user4 varchar(255) NOT NULL,
   url_prefix tinyint(4) DEFAULT '0' NOT NULL,
   randpos int(11) DEFAULT '0' NOT NULL,
   pic_raw_ip tinytext,
   pic_hdr_ip tinytext,
   PRIMARY KEY (pid),
   KEY pic_hits (hits),
   KEY pic_rate (pic_rating),
   KEY aid_approved (aid, approved),
   KEY randpos (randpos),
   KEY pic_aid (aid),
   KEY search (title, caption, keywords, filename)
);

and currently it's at:

CREATE TABLE nuke_cpg_pictures (
   pid int NOT NULL auto_increment,
   aid int DEFAULT '0' NOT NULL,
   filepath varchar(80) NOT NULL,
   filename varchar(50) NOT NULL,
   filesize int DEFAULT '0' NOT NULL,
   total_filesize int DEFAULT '0' NOT NULL,
   pwidth smallint(6) DEFAULT '0' NOT NULL,
   pheight smallint(6) DEFAULT '0' NOT NULL,
   hits int DEFAULT '0' NOT NULL,
   mtime timestamp(6),
   ctime int DEFAULT '0' NOT NULL,
   owner_id int DEFAULT '0' NOT NULL,
   owner_name varchar(40) NOT NULL,
   pic_rating int DEFAULT '0' NOT NULL,
   votes int( DEFAULT '0' NOT NULL,
   title varchar(100) NOT NULL,
   caption text NOT NULL,
   keywords varchar(50) NOT NULL,
   approved enum('YES','NO') DEFAULT 'NO' NOT NULL,
   user1 varchar(50) NOT NULL,
   user2 varchar(50) NOT NULL,
   user3 varchar(50) NOT NULL,
   user4 varchar(50) NOT NULL,
   url_prefix tinyint(4) DEFAULT '0' NOT NULL,
   randpos int DEFAULT '0' NOT NULL,
   pic_raw_ip tinytext,
   pic_hdr_ip tinytext,
   PRIMARY KEY (pid),
   KEY pic_hits (hits),
   KEY pic_rate (pic_rating),
   KEY aid_approved (aid, approved),
   KEY randpos (randpos),
   KEY pic_aid (aid),
   KEY search (title, caption, keywords, filename)
);

Any ideas anyone?

Solution

No column declared TEXT can be indexed. It seems unlikely that you would benefit from

INDEX (title, caption, keywords, filename)

Whereas you might benefit from

FULLTEXT (title, caption, keywords, filename)

But that would mean using this for searching:

WHERE MATCH (title, caption, keywords, filename)
         AGAINST ("...")

See the documentation for what things you can put in the AGAINST clause. FULLTEXT is very good for searching for "words" in one or more (4 in your case) columns. FULLTEXT is happy to handle TEXT column(s); INDEX (aka KEY) is not.

As for the suggestion of using a 'prefix' (eg, caption(100)), you will probably find that it is either useless or counterproductive.

As for changing to INDEX(title, keywords, filename), please show us the query that might want to use that index. It may not be applicable.

If caption is never very long, then change to a VARCHAR with a suitable maximum. Ditto for the other strings.

If you are using CHARACTER SET utf8mb4, you will not be able to have 4 VARCHAR(255) in a single INDEX -- it will hit the 3072 limit. Either shrink the sizes, change the charset, or (probably best), switch to FULLTEXT.

Please provide some kind of sample SELECT.

Other notes:

pic_raw_ip tinytext -- Is that an IP address? VARCHAR(39) is more appropriate; TINYTEXT is almost always ill-advised.

url_prefix sounds like a string, but it is declared tinyint(4) which has a range -128 ti +127. What gives?

filesize int -- Be aware that that is limited to 2GB; probably OK for pictures (this decade), but not for videos.