Argthtjtr

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:

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

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

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

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 wp_postmeta set meta_value = 'outofstock' where meta_key = '_stock_status' and 

    post_id in  

        ( 

          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') 



            )

        )

And also, in wp_postmeta, replace

 KEY `post_id` (`post_id`),

with

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