Improve Query Performance of Select Statment inside IN Statment

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 **Edit2**: After manually changing to innoDB, the explains are changed as below. Explain in location 1: Explain in location 2: Explain in location 3: 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. 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?