Select for this module takes very long over 2.5 on a super server

By Daniel Dumitru, 1 year ago


Select from this module it's huge ! takes very very long ! has to be improved reviewed optimized . rewrotten.


# Query_time: 2.506076 Lock_time: 0.000603 Rows_sent: 0 Rows_examined: 578454

use database;

SET timestamp=1620636904;

SELECT id_order FROM (






    UNIX_TIMESTAMP(o.date_add) as `date_add_ts`,

    o.delivery_date as `delivery_date`,


    IFNULL(c.newsletter, 0) as `newsletter`,,

    (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 ( =

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