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

indexindex-tuningMySQLoptimizationquery-performance

An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one.

Do you think that indexes going to reduce query run time?

Any ideas how can i pass through the error code?

SQL Query takes 10.1 secs and got over 380 times execution in slow log.

SELECT
  l.ID,
  post_title,
  post_content,
  post_name,
  post_parent,
  post_author,
  post_status,
  post_modified_gmt,
  post_date,
  post_date_gmt
FROM
  (
    SELECT
      wp_posts.ID
    FROM
      wp_posts
    WHERE
      wp_posts.post_status IN ('publish')
      AND wp_posts.post_type = 'post'
      AND wp_posts.post_password = ''
      AND wp_posts.post_date != '0000-00-00 00:00:00'
    ORDER BY
      wp_posts.post_modified ASC
    LIMIT
      100 OFFSET 214000
  ) o
  JOIN wp_posts l ON l.ID = o.ID

Indexes that i need to create;

MariaDB [database]> ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_post_type_post_passw_post_statu` (`post_type`,`post_password`,`post_status`);

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

MariaDB [database]> ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_post_modified` (`post_modified`);
Query OK, 453289 rows affected (10.839 sec)            
Records: 453289  Duplicates: 0  Warnings: 0

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
  `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT 0,
  `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`),
  KEY `wp_posts_idx_post_modified` (`post_modified`)
) ENGINE=MyISAM AUTO_INCREMENT=463265 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Best Answer

You mentioned a problem with creating an INDEX, but I see several other problems. Many of them, if fixed, could avoid the index problem.

Huge OFFSET

LIMIT 100 OFFSET 214000

This sounds like a crawler is scraping your site. Block the crawler.

Huge number of "pages"

If it is not a crawler, then please explain who would would have the patience t0 step through to page 2141 !!

More than that, why do you have that many pages?? That is not what WP is designed for.

Can't fully index

Because of this "range", it is not possible to build an truly efficient index for the query:

AND wp_posts.post_date != '0000-00-00 00:00:00'

Do you really have 'zero' dates in your table?

Engine

MyISAM is deprecated and going away; switch to InnoDB. InnoDB has different limitations on indexes, so it may make your index problem go away.

1000

Do you really have long passwords? SELECT MAX(CHAR_LENGTH(post_password)) FROM wp_posts; If it is significantly less than 255, shrink the column definition.

Do you need utf8mb4 for the password? Is it a "plaintext" password; if so, then that charset is probably valid. If it has been hashed, consider ascii, which won't have the 4x issue.

Related Question