0

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
14
  • Show table's DDL.
    – Akina
    Commented May 8, 2020 at 6:47
  • Execute SHOW CREATE TABLE wp_posts and add output to your question. Also show the amount of distinct values in post_type and post_status fields separately.
    – Akina
    Commented May 8, 2020 at 6:49
  • SQL Query takes 10.1 secs - I think that the slow execution problem source is LIMIT 100 OFFSET 214000. This is well-known problem - the more offset value the more execution time.
    – Akina
    Commented May 8, 2020 at 6:55
  • For your query - do not create the index. Existing index type_status_date is good enough for your query - if it is not used then try to force its usage.
    – Akina
    Commented May 8, 2020 at 6:56
  • 1
    If so then type_status_date seems to be enough as I have said above. If this will not help then the problem root is high OFFSET value, and it cannot be healed without some non-standard trick...
    – Akina
    Commented May 8, 2020 at 7:18

1 Answer 1

0

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.

5
  • Its going to be over 50k pages when the imports are done. I converted the storage engine to InnoDB. I dont think its crawlers coz i have that much products. I thought that '0000-00-00 00:00:00' is the date-time format for posts. Are not actually zeros filling my database right? I never used and not planning to use post_password. So does it really worth what the column definition is?
    – execash
    Commented May 10, 2020 at 3:26
  • @execash - In some mode, failing to set a date defaults to '0000-00-00 00:00:00'; I was assuming you were using that as some kind of indicator. The issue is that AND post_date != '0000-00-00 00:00:00' gets in the way of optimization.
    – Rick James
    Commented May 10, 2020 at 5:37
  • @execash - So, where did that huge OFFSET come from, if not a crawler?
    – Rick James
    Commented May 10, 2020 at 5:39
  • @execash - the large string for password is causing the "1000" error.
    – Rick James
    Commented May 10, 2020 at 5:40
  • @execash - If you are not using the password, remove it from the index and, optionally, from the table.
    – Rick James
    Commented May 10, 2020 at 5:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.