Opencart mysql query optimize I am using Opencart 2.x with mysql. I have created a custom module for excel export. My query took more than 2 minutes to execute. So I want to optimize my query.

EXPLAIN shows the following image



This is my query

SELECT o.order_id, 
       Concat(o.firstname, ' ', o.lastname)            AS customer, 
       pov.sku, 
       oo.value, 
       pov.segment, 
       pov.colour, 
       op.quantity, 
       o.shipping_address_1, 
       o.shipping_address_2, 
       o.shipping_city, 
       o.shipping_zone, 
       o.shipping_country, 
       o.shipping_postcode, 
       (SELECT os.NAME 
        FROM   oc_order_status os 
        WHERE  os.order_status_id = o.order_status_id 
               AND os.language_id = '1') AS order_status, 
       (SELECT Count(order_product_id) 
        FROM   oc_order_product 
        WHERE  order_id = o.order_id) AS product_count, 
       (SELECT ot.value 
        FROM   oc_order_total ot 
        WHERE  o.order_id = ot.order_id 
               AND ot.code = 'coupon') AS coupon, 
       (SELECT ot.value 
        FROM   oc_order_total ot 
        WHERE  o.order_id = ot.order_id 
               AND ot.title LIKE '%Shipping Fee%') AS shipping_cost, 
       (SELECT ot.value 
        FROM   oc_order_total ot 
        WHERE  o.order_id = ot.order_id 
               AND ot.title LIKE '%COD Fee (30 INR)%') AS cod, 
       (SELECT ot.value 
        FROM   oc_order_total ot 
        WHERE  o.order_id = ot.order_id 
               AND ot.title = 'CGST') AS cgst, 
       (SELECT ot.value 
        FROM   oc_order_total ot 
        WHERE  o.order_id = ot.order_id 
               AND ot.title = 'SGST') AS sgst, 
       (SELECT ot.value 
        FROM   oc_order_total ot 
        WHERE  o.order_id = ot.order_id 
               AND ot.title LIKE '%Sub-Total%') AS sub_total, 
       o.total, 
       o.currency_code, 
       o.currency_value, 
       o.payment_method, 
       o.date_added, 
       o.date_modified 
FROM   oc_order o 
       LEFT JOIN oc_order_option oo 
              ON ( oo.order_id = o.order_id ) 
       LEFT JOIN oc_product_option_value pov 
              ON ( pov.product_option_value_id = oo.product_option_value_id ) 
       LEFT JOIN oc_order_product op 
              ON( o.order_id = op.order_id ) 
WHERE  o.order_status_id &gt;= 0 
GROUP  BY oo.order_option_id 
ORDER  BY o.order_id DESC
The reason for slow down of query is clearly visible in the query plan. There are columns need to be indexed, to tune the query performance. In your query you have used oc_order_option table which does not have order_id column indexed. Index it using following command:

ALTER TABLE `oc_order_option` ADD KEY `OrderID` (`order_id`)


Same thing is happening with oc_order_product and it needs to be tweaked with following command:

ALTER TABLE `oc_order_product` ADD KEY `ProductOrderID` (`order_id`)


Also you need to tweak oc_order as well, use following:

ALTER TABLE `oc_order` ADD KEY `OrderStatusID` (`order_status_id`)


After implementing the tweaks run your query and see the performance difference.