Wordpress site with around 150000 posts. I have a query that gets executed mulitiple times per page load and it is unreasonably slow - on the order of 2sec:

SELECT COUNT(*) as cnt
FROM wp_postmeta pm, wp_posts p 
inner join wp_posts p2 on p2.ID = p.post_parent and p2.post_status in('order_paid', 'order_received')
WHERE p.ID = pm.post_id
AND ((p.post_status = 'publish'))
AND p.post_type = 'tc_tickets_instances'
AND pm.meta_key = 'ticket_type_id'
AND pm.meta_value IN (157404,157405,155353,155354,155355);

I have installed the WP MySQL For Speed plugin which creates new compound indices and that seems to have improved things slightly. The query runs from 1.5 - 2.5sec "normally" and the page we're loading used to range from 13sec to 24sec before the plugin and from 8 sec to 20 sec after the new indexes are created.

There doesn't seem to be anything obvious to address, but perhaps there is some sneaky technique to optimize this query that I have not found? [![query chart generated with MySQL Workbench][1]][1]

'{  \"query_block\": {.    \"select_id\": 1,
    \"cost_info\": {
      \"query_cost\": \"90915.83\"
    },
    \"nested_loop\": [
      {
        \"table\": {
          \"table_name\": \"p\",
          \"access_type\": \"ref\",
          \"possible_keys\": [
            \"PRIMARY\",
            \"post_parent\",
            \"type_status_date\"
          ],
          \"key\": \"type_status_date\",
          \"used_key_parts\": [
            \"post_type\",
            \"post_status\"
          ],
          \"key_length\": \"164\",
          \"ref\": [
            \"const\",
            \"const\"
          ],
          \"rows_examined_per_scan\": 72012,
          \"rows_produced_per_join\": 72012,
          \"filtered\": \"100.00\",
          \"cost_info\": {
            \"read_cost\": \"10440.29\",
            \"eval_cost\": \"7201.20\",
            \"prefix_cost\": \"17641.49\",
            \"data_read_per_join\": \"253M\"
          },
          \"used_columns\": [
            \"ID\",
            \"post_status\",
            \"post_parent\",
            \"post_type\"
          ]
        }
      },
      {
        \"table\": {
          \"table_name\": \"p2\",
          \"access_type\": \"eq_ref\",
          \"possible_keys\": [
            \"PRIMARY\"
          ],
          \"key\": \"PRIMARY\",
          \"used_key_parts\": [
            \"ID\"
          ],
          \"key_length\": \"8\",
          \"ref\": [
            \"motorco.p.post_parent\"
          ],
          \"rows_examined_per_scan\": 1,
          \"rows_produced_per_join\": 14402,
          \"filtered\": \"20.00\",
          \"cost_info\": {
            \"read_cost\": \"52201.45\",
            \"eval_cost\": \"1440.24\",
            \"prefix_cost\": \"77044.14\",
            \"data_read_per_join\": \"50M\"
          },
          \"used_columns\": [
            \"ID\",
            \"post_status\"
          ],
          \"attached_condition\": \"(`motorco`.`p2`.`post_status` in (\'order_paid\',\'order_received\'))\"
        }
      },
      {
        \"table\": {
          \"table_name\": \"pm\",
          \"access_type\": \"ref\",
          \"possible_keys\": [
            \"PRIMARY\",
            \"meta_key\",
            \"meta_value\"
          ],
          \"key\": \"PRIMARY\",
          \"used_key_parts\": [
            \"post_id\",
            \"meta_key\"
          ],
          \"key_length\": \"1030\",
          \"ref\": [
            \"motorco.p.ID\",
            \"const\"
          ],
          \"rows_examined_per_scan\": 1,
          \"rows_produced_per_join\": 7580,
          \"filtered\": \"50.00\",
          \"cost_info\": {
            \"read_cost\": \"12355.53\",
            \"eval_cost\": \"758.08\",
            \"prefix_cost\": \"90915.83\",
            \"data_read_per_join\": \"7M\"
          },
          \"used_columns\": [
            \"post_id\",
            \"meta_key\",
            \"meta_value\"
          ],
          \"attached_condition\": \"(`motorco`.`pm`.`meta_value` in (157404,157405,155353,155354,155355))\"
        }
      }
    ]
  }
}'

Table defs:

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
  PRIMARY KEY (`post_id`,`meta_key`,`meta_id`),
  UNIQUE KEY `meta_id` (`meta_id`),
  KEY `meta_key` (`meta_key`,`meta_value`(32),`post_id`,`meta_id`),
  KEY `meta_value` (`meta_value`(32),`meta_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1487185 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE `wp_posts` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint 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 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_excerpt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `to_ping` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `pinged` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_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 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_parent` bigint unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `menu_order` int NOT NULL DEFAULT '0',
  `post_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_count` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`),
  KEY `post_parent` (`post_parent`,`post_type`,`post_status`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`post_author`),
  KEY `post_author` (`post_author`,`post_type`,`post_status`,`post_date`)
) ENGINE=InnoDB AUTO_INCREMENT=159698 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

  [1]: .png

Wordpress site with around 150000 posts. I have a query that gets executed mulitiple times per page load and it is unreasonably slow - on the order of 2sec:

SELECT COUNT(*) as cnt
FROM wp_postmeta pm, wp_posts p 
inner join wp_posts p2 on p2.ID = p.post_parent and p2.post_status in('order_paid', 'order_received')
WHERE p.ID = pm.post_id
AND ((p.post_status = 'publish'))
AND p.post_type = 'tc_tickets_instances'
AND pm.meta_key = 'ticket_type_id'
AND pm.meta_value IN (157404,157405,155353,155354,155355);

I have installed the WP MySQL For Speed plugin which creates new compound indices and that seems to have improved things slightly. The query runs from 1.5 - 2.5sec "normally" and the page we're loading used to range from 13sec to 24sec before the plugin and from 8 sec to 20 sec after the new indexes are created.

There doesn't seem to be anything obvious to address, but perhaps there is some sneaky technique to optimize this query that I have not found? [![query chart generated with MySQL Workbench][1]][1]

'{  \"query_block\": {.    \"select_id\": 1,
    \"cost_info\": {
      \"query_cost\": \"90915.83\"
    },
    \"nested_loop\": [
      {
        \"table\": {
          \"table_name\": \"p\",
          \"access_type\": \"ref\",
          \"possible_keys\": [
            \"PRIMARY\",
            \"post_parent\",
            \"type_status_date\"
          ],
          \"key\": \"type_status_date\",
          \"used_key_parts\": [
            \"post_type\",
            \"post_status\"
          ],
          \"key_length\": \"164\",
          \"ref\": [
            \"const\",
            \"const\"
          ],
          \"rows_examined_per_scan\": 72012,
          \"rows_produced_per_join\": 72012,
          \"filtered\": \"100.00\",
          \"cost_info\": {
            \"read_cost\": \"10440.29\",
            \"eval_cost\": \"7201.20\",
            \"prefix_cost\": \"17641.49\",
            \"data_read_per_join\": \"253M\"
          },
          \"used_columns\": [
            \"ID\",
            \"post_status\",
            \"post_parent\",
            \"post_type\"
          ]
        }
      },
      {
        \"table\": {
          \"table_name\": \"p2\",
          \"access_type\": \"eq_ref\",
          \"possible_keys\": [
            \"PRIMARY\"
          ],
          \"key\": \"PRIMARY\",
          \"used_key_parts\": [
            \"ID\"
          ],
          \"key_length\": \"8\",
          \"ref\": [
            \"motorco.p.post_parent\"
          ],
          \"rows_examined_per_scan\": 1,
          \"rows_produced_per_join\": 14402,
          \"filtered\": \"20.00\",
          \"cost_info\": {
            \"read_cost\": \"52201.45\",
            \"eval_cost\": \"1440.24\",
            \"prefix_cost\": \"77044.14\",
            \"data_read_per_join\": \"50M\"
          },
          \"used_columns\": [
            \"ID\",
            \"post_status\"
          ],
          \"attached_condition\": \"(`motorco`.`p2`.`post_status` in (\'order_paid\',\'order_received\'))\"
        }
      },
      {
        \"table\": {
          \"table_name\": \"pm\",
          \"access_type\": \"ref\",
          \"possible_keys\": [
            \"PRIMARY\",
            \"meta_key\",
            \"meta_value\"
          ],
          \"key\": \"PRIMARY\",
          \"used_key_parts\": [
            \"post_id\",
            \"meta_key\"
          ],
          \"key_length\": \"1030\",
          \"ref\": [
            \"motorco.p.ID\",
            \"const\"
          ],
          \"rows_examined_per_scan\": 1,
          \"rows_produced_per_join\": 7580,
          \"filtered\": \"50.00\",
          \"cost_info\": {
            \"read_cost\": \"12355.53\",
            \"eval_cost\": \"758.08\",
            \"prefix_cost\": \"90915.83\",
            \"data_read_per_join\": \"7M\"
          },
          \"used_columns\": [
            \"post_id\",
            \"meta_key\",
            \"meta_value\"
          ],
          \"attached_condition\": \"(`motorco`.`pm`.`meta_value` in (157404,157405,155353,155354,155355))\"
        }
      }
    ]
  }
}'

Table defs:

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
  PRIMARY KEY (`post_id`,`meta_key`,`meta_id`),
  UNIQUE KEY `meta_id` (`meta_id`),
  KEY `meta_key` (`meta_key`,`meta_value`(32),`post_id`,`meta_id`),
  KEY `meta_value` (`meta_value`(32),`meta_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1487185 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE `wp_posts` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint 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 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_excerpt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `to_ping` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `pinged` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_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 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_parent` bigint unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `menu_order` int NOT NULL DEFAULT '0',
  `post_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_count` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`),
  KEY `post_parent` (`post_parent`,`post_type`,`post_status`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`post_author`),
  KEY `post_author` (`post_author`,`post_type`,`post_status`,`post_date`)
) ENGINE=InnoDB AUTO_INCREMENT=159698 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

  [1]: https://i.sstatic/QurVFDnZ.png

• mysql
• wordpress
• performance
• query-optimization

• FROM wp_postmeta pm, wp_posts p inner join wp_posts p2 on p2.ID = p.post_parent, please use proper join syntax, I do not understand what is joined here . As per the performance please add table definitions and execution plan run explain format=json select... – Ergest Basha Commented Feb 14 at 18:58
• I added the requested info. As to the syntax of the inner join - not sure how to make it more explicit. For the syntax with which I am familiar, this is standard. It's joining on a relationship of post_parent (which in WP is the ID of another "parent" row) and relevant status of these rows. To add further, I'm not super hopeful that there even is a way to fix this query (WP db design is notoriously awful). I have a project to replace the WP schema for this data with a proper relational design (parallel to WP) - but that's many months away :( – jeromio Commented Feb 15 at 0:53
• Comma separated list of tables and providing the join criteria in the where clause is not standard for about 30-40 years... Mixing comma separated list tables with explicit join can also have unintended consequences as join operator has higher precedence then comma operator. – Shadow Commented Feb 15 at 7:49
• You have no index on the post table that would help with any of the two joins and the filter criteria at the same tume – Shadow Commented Feb 15 at 8:36
• I'm one of the authors of that Index WP MySQL plugin. Gack, that is an horrendous query with an equally horrendous query plan. What pluign generated it? Let me know, maybe hit the plugin with a support topic(, and we can gang up on that plugin author and get them to fix it. – O. Jones Commented Feb 15 at 17:13

2 Answers 2

I have noticed some issues which might affect performance.

Do not mix comma separated and explicit JOIN, it might produce unexpected results. Use proper JOIN syntax.

FROM wp_postmeta pm, wp_posts p #### ----> Here is the comma separated 

The column meta_value is longtext and in the query you are using integer rather than a string

Replace

AND pm.meta_value IN (157404,157405,155353,155354,155355)

With

AND pm.meta_value IN ('157404','157405','155353','155354','155355')

After the changes your query should look something like

SELECT COUNT(*) as cnt
FROM wp_postmeta pm
INNER JOIN wp_posts p ON p.ID = pm.post_id
INNER JOIN wp_posts p2 on p2.ID = p.post_parent 
WHERE p.post_status = 'publish'
AND p.post_type = 'tc_tickets_instances'
AND p2.post_status in('order_paid', 'order_received')
AND pm.meta_key = 'ticket_type_id'
AND pm.meta_value IN ('157404','157405','155353','155354','155355');

Add the following indexes and see if it makes any difference

ALTER TABLE `wp_postmeta` ADD INDEX `pid_mk_mv` (`post_id`,`meta_key`,`meta_value`(32));
ALTER TABLE `wp_posts` ADD INDEX `pid_mk_mv` (`ID`,`post_parent`,`post_type`,`post_status`);

You are joining the wp_posts table twice using INNER JOIN. This unnecessarily complicates the query. Instead, you can join the wp_posts table once and filter related posts using the post_parent column.

SELECT COUNT(*) AS cnt
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'tc_tickets_instances'
  AND pm.meta_key = 'ticket_type_id'
  AND pm.meta_value IN ('157404','157405','155353','155354','155355')
  AND p.post_status = 'publish'
  AND (
    p.post_parent IS NULL OR p.post_parent IN (
      SELECT ID
      FROM wp_posts
      WHERE post_status IN ('order_paid', 'order_received')
    )
  );

Alternatively, instead of connecting tables with JOIN, you can use nested SELECT.

SELECT COUNT(*) AS cnt
FROM wp_postmeta pm
WHERE pm.meta_key = 'ticket_type_id'
  AND pm.meta_value IN ('157404','157405','155353','155354','155355')
  AND pm.post_id IN (
    SELECT p.ID
    FROM wp_posts p
    WHERE p.post_type = 'tc_tickets_instances'
      AND p.post_status = 'publish'
      AND (
        p.post_parent IS NULL
        OR p.post_parent IN (
          SELECT ID
          FROM wp_posts
          WHERE post_status = 'order_paid' OR post_status = 'order_received'
        )
      )
  );

Validate query results, and compare speed performance of queries in your own database.