mysql - Troubleshooting Wordpress/Woocommerce custom SQL query for reporting -

hopefully right forum, question seems overlap stack exchange community seemed best.

i have custom reports woocommerce orders on wordpress site. have 1 query freezing locally, meaning in localhost cpu goes 100% , never finishes , don't understand why. point here query:

select sum(postmeta.meta_value) pca_postmeta postmeta left join pca_woocommerce_order_items orders on orders.order_id = postmeta.post_id postmeta.meta_key = '_order_total' , orders.order_item_id in (     select item_meta.order_item_id      pca_woocommerce_order_itemmeta item_meta      left join pca_woocommerce_order_items orders on item_meta.order_item_id = orders.order_item_id      left join pca_posts posts on posts.id = orders.order_id      item_meta.meta_value = '23563'      , posts.post_status in ('wc-processing','wc-completed')      group orders.order_id ) 

as can see goal here summation of orders specific campaign (23563). nested query works expected on own, returning list of ids so:

note: little curious if 2.6289 secs long when returned 65 total, although there 148220 total

the problem query doesn't seem nested part. suggestions? different approach in mind?

p.s. use nested query @ other times represent orders campaign id in php reporting class. question php has nothing it.

update/follow up:

is possible convert join described here: using select statement within clause ? i'm little light on sql not sure how seems promising

group  orders.order_id 

does not make sense because selecting order_item_id.

pca_woocommerce_order_itemmeta benefit from

index(meta_value, order_item_id) 

an might equivalent query, avoiding in(select...):

select  sum(pm.meta_value)             ( select  im.order_item_id              pca_woocommerce_order_itemmeta im             left join  pca_woocommerce_order_items o                              on im.order_item_id = o.order_item_id             left join  pca_posts posts on posts.id = o.order_id              im.meta_value = '23563'               ,  posts.post_status in ('wc-processing','wc-completed')             group  o.order_id        ) w     join  pca_woocommerce_order_items o on w.order_item_id = o.order_item_id     join  pca_postmeta pm on o.order_id = pm.post_id      pm.meta_key = '_order_total' 

edit

some principles behind did. here guessing @ optimizer various possible formulations of query.

• i got rid of left -- may have changed output. needed avoid left join ( select ... ) not optimizable.
• by having one subquery in list of "tables" being joined, optimizer (almost certainly) start subquery , "nested loop joins" other tables. nlj common way perform query.
• a subselect has no index, needs first in order, else inefficient.
• without subqueries, optimizer likes start whichever table has in where clause.
• the requirement start subquery "table" stronger desire pick table based on where pm.meta_key = '_order_total'.
• inside subquery, "=" test (where im.meta_value = '23563) provides starting point set of joins. further enhanced not being 'right' of left join. hence, suggested index.