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
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, ' ') 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
,' > '
,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
,'' 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;
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;
Best Answer
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 .. ONsyntax - good. Please do the same for the second.)Add composite indexes where appropriate. For example:
pd:INDEX(language_id, product_id). Please provideSHOW CREATE TABLEso 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 doo.date_added >= '2014-12-01' AND o.date_added < '2015-11-01'.In the
EXPLAIN, the59780together withUsing indexsuggests 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:
Perhaps you meant
That showed up in the
EXPLAINas an unexpectedly big number of rows forc.