Improve Query Performance of Select Statment inside IN Statment

By Posted on

Question :

My query is:

(3) UPDATE wp_postmeta set meta_value = 'outofstock' where meta_key = '_stock_status' and 
    post_id in  
        ( 
         (2)SELECT post_id FROM 
            ( 
            (1) SELECT A.post_id from wp_postmeta A 
                JOIN wp_postmeta B ON A.post_id = B.post_id 
                AND A.meta_key = 'attribute_pa_beden' 
                and A.meta_value in ('12yas','34yas','56yas','78yas','910yas','1112yas') 
                and B.meta_key = 'attribute_pa_renk' 
                and ((B.meta_value = 'bordo') OR ('bordo' = 'hepsi')) 
                JOIN wp_posts ON A.post_id = wp_posts.id 
                JOIN wp_term_relationships ON wp_posts.post_parent = wp_term_relationships.object_id 
                and term_taxonomy_id in ('2643','2304') 
            ) AS DerivedTable
        )

To improve the speed of this query, I analysed it using the “Explain” statment. Below are the results:

When I added explain to (1) location in above query and run the subquery. The results are as below:

enter image description here

When I added explain to (2) location and run that subqyery, results are as below.

enter image description here

When I added explain to (3) location and run the whole query, results are as below:

enter image description here

My analysis is there is no speed problem with the (1) subquery, but after I select the data from this subquery to a derived table (2), somehow there is a 55.277.640 “rows” comes, and which seems to be the reason why my query is so slow. How can I optimize it ? What is wrong here ?

Edit: The tables are WordPress WooCommerce module standart tables. I didn’t modified them. Here SHOW CREATE TABLE results:

wp_postmeta 

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM AUTO_INCREMENT=11119572 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

wp_posts

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`)
) ENGINE=MyISAM AUTO_INCREMENT=352598 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

wp_term_relationships

CREATE TABLE `wp_term_relationships` (
  `object_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `term_order` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`object_id`,`term_taxonomy_id`),
  KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Edit2: After manually changing to innoDB, the explains are changed as below.

Explain in location 1:

enter image description here

Explain in location 2:

enter image description here

Explain in location 3:

enter image description here

Here some information on what I am trying to do. In my website there are a lot of products variations. The wp_postmeta table has meta information for all variations. (For example, price, color, size, instock etc).

My query is used to update stock information. So for example, I have a product with 9 colors, and 9 size. Which means 81 variations. If I need to update stocks of color ‘bordo’ for all sizes. This means it is 9 rows has to be updated in wp_postmeta. (All sizes for color: bordo).

Here some row numbers if I update above queries to select count(*):

wp_postmeta has 9.929.761

The first subquery where I indicated as (1), returns 3612 rows.

The first subquery where I indicated as (2), returns 3612 rows as
well.

The first subquery where I indicated as (3), returns 3612 rows too.

So basicly there are 3612 “bordo” colored product variations in my website, and I need to update stock information of all these variations.

When I checked my database, there are 227K rows with meta_key “_stock_status”. (All colors and all product sizes). I know the cost of updating all that data is a little bit costly. But I am not using this function all the time.

When I add 20 new products and when I get information from production team about latest stock status of products and colors, I click this “Update Stocks” button to update all stock information in the website to the last state.

Edit2

I read the modification requirments for wp_postmeta table from Rick. They all seemed to be helpful but I didn’t wanted to change the default structure of wp_postmeta table as I am not a DB expert and may not revert back future problems that can be caused by modifing the core wordpress tables.

So to stay on the safe side, I read the other two answers from ypercubeᵀᴹ and Old Pro.

They suggested to add a new index to wp_postmeta table, and this is my only change on this core wordpress table. After I tested both of their queries, and they gave the same explain statement results as below.

enter image description here

So basicly their query is doing the same thing with different structure. Now the query’s performance has increased quite much. The only question in my mind, how safe it is to add a manual index to a wordpress core table?

Answer :

postmeta has inefficient. See https://stackoverflow.com/questions/43859351/why-are-references-to-wp-postmeta-so-slow
for a discussion of what to do about it. Or: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta or
https://wordpress.stackexchange.com/questions/248207/simple-sql-query-on-wp-postmeta-very-slow

First, the WordPress database design has several flaws as Rick James points out in his answer and the linked ones. The wp_postmeta in particular is a common cause of performance problems in many WP installations, as soon they grow enough and have more than a few thousands rows.

Before going the difficult and long road of addressing that, I’ll suggest something that might help improving the specific query in the short time:

Rewrite the IN (complex subquery) to a JOIN:

UPDATE
    ( 
        SELECT A.post_id from wp_postmeta A 
        JOIN wp_postmeta B ON A.post_id = B.post_id 
        AND A.meta_key = 'attribute_pa_beden' 
        and A.meta_value in ('12yas','34yas','56yas','78yas','910yas','1112yas') 
        and B.meta_key = 'attribute_pa_renk' 
        and ((B.meta_value = 'bordo') OR ('bordo' = 'hepsi')) 
        JOIN wp_posts ON A.post_id = wp_posts.id 
        JOIN wp_term_relationships ON wp_posts.post_parent = wp_term_relationships.object_id 
        and term_taxonomy_id in ('2643','2304') 
    ) AS DerivedTable
    JOIN
        wp_postmeta AS upd
    ON
        upd.post_id = DerivedTable.post_id
SET
    upd.meta_value = 'outofstock'
WHERE
    upd.meta_key = '_stock_status' ;

and add an index on wp_postmeta (meta_key(191), post_id)

While what I am proposing is not necessarily the best idea in the general case, I think for this specific situation you would be better off using nested IN clauses rather than the complicated self-join. Try this:

  UPDATE 
        ( 
          SELECT B.post_id FROM wp_postmeta B where B.meta_key = 'attribute_pa_renk' 
                and ((B.meta_value = 'bordo') OR ('bordo' = 'hepsi')) 
                and B.post_id in 
            ( 
              SELECT A.post_id from wp_postmeta A 
                JOIN wp_posts ON A.post_id = wp_posts.id 
                JOIN wp_term_relationships 
                  ON wp_posts.post_parent = wp_term_relationships.object_id 
                and term_taxonomy_id in ('2643','2304') 
              WHERE A.meta_key = 'attribute_pa_beden' 
                and A.meta_value in
                     ('12yas','34yas','56yas','78yas','910yas','1112yas') 
            )
        ) AS targets
  JOIN wp_postmeta ON wp_postmeta.post_id = targets.post_id
  SET meta_value = 'outofstock' 
  WHERE meta_key = '_stock_status'

And also, in wp_postmeta, replace

 KEY `post_id` (`post_id`),

with

 KEY `post_id` (`post_id`, `meta_key`(191)),

Leave a Reply

Your email address will not be published. Required fields are marked *