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

0 votes
1 answer
777 views
mysql index query-performance optimization index-tuning
                          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
                        
Asked by execash (1 rep)
May 8, 2020, 06:44 AM
Last activity: Aug 6, 2025, 01:04 PM
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Related Questions
