Select for this module takes very long over 2.5 on a super server
open
Hello,
Select from this module it's huge ! takes very very long ! has to be improved reviewed optimized . rewrotten.
Daniel
# Query_time: 2.506076 Lock_time: 0.000603 Rows_sent: 0 Rows_examined: 578454
use database;
SET timestamp=1620636904;
SELECT id_order FROM (
SELECT
o.id_order,
o.reference,
o.id_customer,
o.date_add,
UNIX_TIMESTAMP(o.date_add) as `date_add_ts`,
o.delivery_date as `delivery_date`,
o.current_state,
IFNULL(c.newsletter, 0) as `newsletter`,
c.email,
(e.id_email IS NOT NULL AND NULLIF(e.error,'') IS NULL) as `sent`,
NULLIF(e.error, '') as `error`,
TRIM(CONCAT(c.firstname, ' ', c.lastname)) as `customer_name`,
(SELECT COUNT(1) FROM ps_order_detail d WHERE d.id_order = o.id_order) as `total_items`,
(SELECT COUNT(1) FROM ps_order_detail d INNER JOIN ps_product p ON (d.product_id = p.id_product) WHERE d.id_order = o.id_order AND 1) as `items`,
(SELECT COUNT(DISTINCT r.id_entity) FROM ps_revws_review r, ps_order_detail d WHERE d.id_order=o.id_order AND d.product_id = r.id_entity AND r.entity_type = 'product' AND o.id_customer = r.id_customer AND r.deleted=0 AND 1) `reviewed_items`,
1 as `valid_group`,
(c.id_customer IS NOT NULL AND IFNULL(s.subscribed, 1)) as `consent`
FROM ps_orders o
LEFT JOIN ps_customer c ON (c.id_customer = o.id_customer AND c.id_shop IN (1) )
LEFT JOIN ps_revws_subscription s ON (s.email = c.email)
LEFT JOIN ps_revws_email e ON (e.entity_type = 'order' AND e.id_entity = o.id_order AND e.id_email = (SELECT MAX(id_email) FROM ps_revws_email e2 where e2.entity_type = 'order' AND e2.id_entity = o.id_order))
ORDER BY id_order
) as `inner` WHERE NOT(sent) AND error IS NULL AND date_add_ts > 1574632800 AND consent AND items > 0 AND items > reviewed_items AND valid_group AND date_add < DATE_SUB(NOW(), INTERVAL 3 DAY) AND date_add > DATE_SUB(NOW(), INTERVAL 90 DAY) AND current_state IN (5) AND delivery_date > '1901-01-01' AND delivery_date < DATE_SUB(NOW(), INTERVAL 3 DAY);