1

I have been having issues with 2 queries with query execution times up to 250 seconds for each query. The tables joined have each less than 10,000 rows, with exception to 2 tables that have around 60k and 30k rows each.

I am in learning stages of MySQL where I googled for writing each query. I googled about this and some of them suggest using an inner join is better, but I get the same result in both inner join and left join.

What can I do in order to get the results quickly.

First Query

This is not that complex but it is taking around 200 Seconds to Execute

SELECT DISTINCT p.ean
    ,p.isbn
    ,p.mpn
    ,p.sku
    ,p.minimum
    ,p.upc
    ,p.subtract
    ,p.sort_order
    ,p.model
    ,p.price AS normal_price
    ,p.IMAGE
    ,p.shipping
    ,p.weight
    ,p.weight_class_id
    ,p.date_modified
    ,p.quantity
    ,p.tax_class_id
    ,p.stock_status_id
    ,p.product_id AS id
    ,p.STATUS
    ,sta.NAME AS status_name
    ,pd.NAME AS products_name
    ,pd.description AS product_description
    ,pd.language_id
    ,pd.product_id
    ,pd.tag AS product_tags
    ,m.NAME AS manufacturer_name
    ,cd.NAME AS category_name
    ,cd.category_id
    ,cd.language_id
    ,st.store_id
FROM oc_product p
INNER JOIN oc_product_description pd ON p.product_id = pd.product_id
INNER JOIN oc_product_to_category p2c ON p.product_id = p2c.product_id
INNER JOIN oc_category c ON c.category_id
INNER JOIN oc_category_description cd ON cd.category_id = p2c.category_id
INNER JOIN oc_manufacturer m ON p.manufacturer_id = m.manufacturer_id
INNER JOIN oc_stock_status sta ON p.stock_status_id = sta.stock_status_id
INNER JOIN oc_product_to_store st ON p.product_id = st.product_id
WHERE p.STATUS = 1
    AND pd.language_id = 1
    AND sta.language_id = 1
    AND cd.language_id = 1
    AND st.store_id = 0
GROUP BY p.product_id
ORDER BY p.product_id ASC LIMIT 270
    ,10

1st Query Explain Results

Second Query

This is taking around 240-250 Seconds

SELECT o.date_added AS DATE
    ,YEAR(o.date_added) AS year
    ,QUARTER(o.date_added) AS quarter
    ,MONTHNAME(o.date_added) AS month
    ,MIN(o.date_added) AS date_start
    ,MAX(o.date_added) AS date_end
    ,op.order_id
    ,op.product_id
    ,qa.order_product_id
    ,(
        SELECT p.IMAGE
        FROM oc_product p
        WHERE op.product_id = p.product_id
        ) AS IMAGE
    ,(
        SELECT p.sku
        FROM oc_product p
        WHERE op.product_id = p.product_id
        ) AS sku
    ,op.NAME
    ,op.model
    ,(
        SELECT cd.NAME
        FROM oc_category_description cd
            ,oc_category c
            ,oc_product_to_category p2c
        WHERE c.category_id = cd.category_id
            AND p2c.category_id = c.category_id
            AND cd.language_id = '1'
            AND op.product_id = p2c.product_id
        GROUP BY op.product_id
        ) AS category
    ,(
        SELECT p.manufacturer_id
        FROM oc_product p
        WHERE op.product_id = p.product_id
        ) AS manufacturer_id
    ,(
        SELECT m.NAME
        FROM oc_manufacturer m
            ,oc_product p
        WHERE p.manufacturer_id = m.manufacturer_id
            AND op.product_id = p.product_id
        ) AS manufacturer
    ,(
        SELECT GROUP_CONCAT(CONCAT (
                    agd.NAME
                    ,'  >  '
                    ,ad.NAME
                    ,'  >  '
                    ,pa.TEXT
                    ) ORDER BY agd.NAME, ad.NAME, pa.TEXT ASC SEPARATOR '<br>')
        FROM oc_product_attribute pa
            ,oc_attribute_description ad
            ,oc_attribute a
            ,oc_attribute_group_description agd
        WHERE pa.language_id = '1'
            AND pa.product_id = op.product_id
            AND pa.attribute_id = ad.attribute_id
            AND ad.language_id = '1'
            AND ad.attribute_id = a.attribute_id
            AND a.attribute_group_id = agd.attribute_group_id
            AND agd.language_id = '1'
        ) AS attribute
    ,(
        SELECT p.STATUS
        FROM oc_product p
        WHERE op.product_id = p.product_id
        ) AS STATUS
    ,(
        SELECT p.quantity
        FROM oc_product p
        WHERE op.product_id = p.product_id
        ) AS stock_quantity
    ,(
        SELECT GROUP_CONCAT(IFNULL(pov.quantity, '&nbsp;') ORDER BY oo.order_option_id SEPARATOR '<br>')
        FROM oc_order_option oo
            ,oc_product_option_value pov
        WHERE op.order_product_id = oo.order_product_id
            AND op.product_id = pov.product_id
            AND pov.product_option_value_id = oo.product_option_value_id
        ) AS stock_oquantity
    ,SUM(op.quantity) AS sold_quantity
    ,op.price
    ,o.currency_code
    ,o.currency_value
    ,SUM(op.total) AS total_excl_vat
    ,SUM(op.tax * op.quantity) AS tax
    ,SUM(op.total + (op.tax * op.quantity)) AS total_incl_vat
    ,SUM(op.total) AS sales
    ,SUM(op.cost * op.quantity) AS costs
    ,SUM(op.total - op.cost * op.quantity) AS profit
    ,options
    ,oovalue
    ,ooname
    ,(
        SELECT SUM(op.quantity)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS sold_quantity_total
    ,(
        SELECT SUM(op.total)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS total_excl_vat_total
    ,(
        SELECT SUM(op.tax * op.quantity)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS tax_total
    ,(
        SELECT SUM(op.total + (op.tax * op.quantity))
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS total_incl_vat_total
    ,(
        SELECT SUM(op.total)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS sales_total
    ,(
        SELECT SUM(op.cost * op.quantity)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS costs_total
    ,(
        SELECT SUM(op.total - op.cost * op.quantity)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS profit_total
FROM (
    SELECT oo.order_product_id
        ,GROUP_CONCAT(NAME, value, type ORDER BY NAME, value, type) AS options
        ,GROUP_CONCAT(value SEPARATOR '<br>') AS oovalue
        ,GROUP_CONCAT(NAME SEPARATOR ':<br>') AS ooname
    FROM oc_order_option oo
    WHERE (
            type = 'radio'
            OR type = 'checkbox'
            OR type = 'select'
            OR type = 'image'
            OR type = 'colour'
            OR type = 'size'
            OR type = 'multiple'
            )
    GROUP BY order_product_id
    ) qa
    ,oc_order_product op
    ,oc_order o
WHERE op.order_product_id = qa.order_product_id
    AND op.order_id = o.order_id
    AND (
        DATE (o.date_added) >= '2014-12-01'
        AND DATE (o.date_added) <= '2015-10-31'
        )
    AND o.order_status_id > 0
GROUP BY options
    ,op.product_id

UNION ALL

SELECT o.date_added AS DATE
    ,YEAR(o.date_added) AS year
    ,QUARTER(o.date_added) AS quarter
    ,MONTHNAME(o.date_added) AS month
    ,MIN(o.date_added) AS date_start
    ,MAX(o.date_added) AS date_end
    ,op.order_id
    ,op.product_id
    ,op.order_product_id
    ,(
        SELECT p.IMAGE
        FROM oc_product p
        WHERE op.product_id = p.product_id
        ) AS IMAGE
    ,(
        SELECT p.sku
        FROM oc_product p
        WHERE op.product_id = p.product_id
        ) AS sku
    ,op.NAME
    ,op.model
    ,(
        SELECT cd.NAME
        FROM oc_category_description cd
            ,oc_category c
            ,oc_product_to_category p2c
        WHERE c.category_id = cd.category_id
            AND p2c.category_id = c.category_id
            AND cd.language_id = '1'
            AND op.product_id = p2c.product_id
        GROUP BY op.product_id
        ) AS category
    ,(
        SELECT p.manufacturer_id
        FROM oc_product p
        WHERE op.product_id = p.product_id
        ) AS manufacturer_id
    ,(
        SELECT m.NAME
        FROM oc_manufacturer m
            ,oc_product p
        WHERE p.manufacturer_id = m.manufacturer_id
            AND op.product_id = p.product_id
        ) AS manufacturer
    ,(
        SELECT GROUP_CONCAT(CONCAT (
                    agd.NAME
                    ,'&nbsp;&nbsp;&gt;&nbsp;&nbsp;'
                    ,ad.NAME
                    ,'&nbsp;&nbsp;&gt;&nbsp;&nbsp;'
                    ,pa.TEXT
                    ) ORDER BY agd.NAME, ad.NAME, pa.TEXT ASC SEPARATOR '<br>')
        FROM oc_product_attribute pa
            ,oc_attribute_description ad
            ,oc_attribute a
            ,oc_attribute_group_description agd
        WHERE pa.language_id = '1'
            AND pa.product_id = op.product_id
            AND pa.attribute_id = ad.attribute_id
            AND ad.language_id = '1'
            AND ad.attribute_id = a.attribute_id
            AND a.attribute_group_id = agd.attribute_group_id
            AND agd.language_id = '1'
        ) AS attribute
    ,(
        SELECT p.STATUS
        FROM oc_product p
        WHERE op.product_id = p.product_id
        ) AS STATUS
    ,(
        SELECT p.quantity
        FROM oc_product p
        WHERE op.product_id = p.product_id
        ) AS stock_quantity
    ,'' AS stock_oquantity
    ,SUM(op.quantity) AS sold_quantity
    ,op.price
    ,o.currency_code
    ,o.currency_value
    ,SUM(op.total) AS total_excl_vat
    ,SUM(op.tax * op.quantity) AS tax
    ,SUM(op.total + (op.tax * op.quantity)) AS total_incl_vat
    ,SUM(op.total) AS sales
    ,SUM(op.cost * op.quantity) AS costs
    ,SUM(op.total - op.cost * op.quantity) AS profit
    ,'' AS options
    ,'' AS oovalue
    ,'' AS ooname
    ,(
        SELECT SUM(op.quantity)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS sold_quantity_total
    ,(
        SELECT SUM(op.total)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS total_excl_vat_total
    ,(
        SELECT SUM(op.tax * op.quantity)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS tax_total
    ,(
        SELECT SUM(op.total + (op.tax * op.quantity))
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS total_incl_vat_total
    ,(
        SELECT SUM(op.total)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS sales_total
    ,(
        SELECT SUM(op.cost * op.quantity)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS costs_total
    ,(
        SELECT SUM(op.total - op.cost * op.quantity)
        FROM oc_order_product op
            ,oc_order o
        WHERE op.order_id = o.order_id
            AND (
                DATE (o.date_added) >= '2014-12-01'
                AND DATE (o.date_added) <= '2015-10-31'
                )
            AND o.order_status_id > 0
        ) AS profit_total
FROM oc_order_product op
    ,oc_order o
WHERE op.order_id = o.order_id
    AND

IF (
        op.order_product_id NOT IN (
            SELECT order_product_id
            FROM oc_order_option
            )
        ,op.order_product_id NOT IN (
            SELECT order_product_id
            FROM oc_order_option
            )
        ,op.order_product_id IN (
            SELECT order_product_id
            FROM oc_order_option
            GROUP BY order_product_id
            HAVING SUM(product_option_value_id) = 0
            )
        )
    AND (
        DATE (o.date_added) >= '2014-12-01'
        AND DATE (o.date_added) <= '2015-10-31'
        )
    AND o.order_status_id > 0 GROUP BY op.product_id ORDER BY sold_quantity DESC
    ,sales DESC;

2nd Query Explain Results

Please note that when i checked profiling option 98% of the time taken is for copying to tmp table.

the heap table and tmp table size is:

SET max_heap_table_size = 1024 * 1024 * 132;

SET tmp_table_size = 1024 * 1024 * 132;

1

In the first query, "pagination via OFFSET" is at least part of the performance problem. In your example, your query must scan 270+10 rows to find the 10 needed. If, instead, you "remembered where you left off", you might be able to look at only 10 rows. More discussion.

(In the first query, you used JOIN .. ON syntax - good. Please do the same for the second.)

Add composite indexes where appropriate. For example: pd: INDEX(language_id, product_id). Please provide SHOW CREATE TABLE so we can discuss this further.

With all the 'attribute' and 'language' tables, I suspect this is an EAV schema that is "over-normalized". Again, seeing the schema will help discuss this.

I see two subqueries referencing oc_product to get two columns (IMAGE and sku). A JOIN to oc_product would probably be more efficient.

Don't hide date_added inside a function (DATE()), Instead do o.date_added >= '2014-12-01' AND o.date_added < '2015-11-01'.

In the EXPLAIN, the 59780 together with Using index suggests that it did a full index scan to find the answer. Probably need a better index (and not hide the column in a function).

IN ( SELECT ... ) is inefficient, can it be written another way?

Here's a big mistake that is hurting the first query:

INNER JOIN oc_category c ON c.category_id

Perhaps you meant

INNER JOIN oc_category c ON c.category_id = p.category_id

That showed up in the EXPLAIN as an unexpectedly big number of rows for c.

  • Thanks for the answer. I cant believe it. The first query time went down from 200+ seconds to 0.34 seconds when i added INNER JOIN oc_category c ON c.category_id = p.category_id . – Syed Mudabbir Dec 28 '15 at 18:58
  • Yep. You were looking through 1K rows instead of 1. (See the 1021 on the last line of the EXPLAIN.) – Rick James Dec 28 '15 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.