Otimização de query Wordpress em um banco Mysql

É Necessário refazer o select interno (select dentro dos campos de retorno do select externo) da query abaixo de outra forma, pois em um banco de testes este select elevou o tempo de processamento da query de 0,3seg para 15seg.

No banco de produção está levando mais de 60 seg, ocasionando timeout.

Select p.id as 'id', p.post_date as 'Data', p.post_status as 'Status', j0.meta_value as 'customer_id', j1.meta_value as 'Frete', j2.meta_value as 'Total', j3.meta_value as 'Forma_de_pagamento', j7.Numero_de_items as Numero_de_items, Ifnull(j8.display_name, '') as afiliado, (select count(*) from irwpy_posts px join irwpy_postmeta pm on pm.post_id=px.id WHERE (px.post_status='wc-completed' OR px.post_status='wc-despachado' OR px.post_status='wc-transito' OR px.post_status='wc-processing') AND pm.meta_key='_customer_user' AND pm.meta_value=customer_id) Pedidosaprovados
from irwpy_posts as p
join (select m0.Post_id, m0.meta_key, m0.meta_value from irwpy_postmeta as m0 where m0.meta_key = '_customer_user') j0 On (p.id = j0.post_id)
join (select m1.Post_id, m1.meta_key, m1.meta_value from irwpy_postmeta as m1 where m1.meta_key = '_order_shipping') j1 On (p.id = j1.post_id)
join (select m2.Post_id, m2.meta_key, m2.meta_value from irwpy_postmeta as m2 where m2.meta_key = '_order_total') j2 On (p.id = j2.post_id)
join (select m3.Post_id, m3.meta_key, m3.meta_value from irwpy_postmeta as m3 where m3.meta_key = '_payment_method_title') j3 On (p.id = j3.post_id)
join (
select *, sum(m7.Itens) as Numero_de_items from (
select m4.order_id, m4.order_item_id, m4.order_item_name,
(CASE m4.order_item_name
WHEN 'PsoriOff - 1 unidade' THEN 1 * j6.meta_value
WHEN 'PsoriOff - 3 unidades' THEN 3 * j6.meta_value
WHEN 'PsoriOff - 5 unidades' Then 5 * j6.meta_value
end) as itens
from irwpy_woocommerce_order_items as m4
join (
select m5.Order_item_id, m5.meta_key, m5.meta_value
from irwpy_woocommerce_order_itemmeta as m5
where m5.meta_key = '_qty') j6
ON (m4.order_item_id = j6.order_item_id
)
Where m4.order_item_type = 'line_item'
) as m7
group by m7.order_id
) j7 On (p.id = j7.order_id)
left join (
select m3.Display_name, m1.reference from irwpy_affiliate_wp_referrals m1
join irwpy_affiliate_wp_affiliates m2 on m2.Affiliate_id=m1.affiliate_id
join irwpy_users m3 on m3.ID=m2.user_id
) j8 ON (p.id=j8.reference)
WHERE p.post_type = 'shop_order'
AND p.post_status != 'trash'
And p.post_date >= date(now()) - interval 100 day
order by p.post_date DESC;