I just learning MYSQL, I have MySql subquery like this:
EXPLAIN EXTENDED SELECT brand_name, stars, hh_stock, hh_stock_value, sales_monthly_1, sales_monthly_2, sales_monthly_3, sold_monthly_1, sold_monthly_2,
sold_monthly_3, price_uvp, price_ecp, price_default, price_margin AS margin, vc_percent as vc, cogs, products_length, products_id, material_expenses,
MAX(price) AS products_price, SUM(total_sales) AS total_sales,
IFNULL(MAX(active_age), DATEDIFF(NOW(), products_date_added)) AS products_age, DATEDIFF(NOW(), products_date_added) AS jng_products_age,
AVG(sales_weekly) AS sales_weekly, AVG(sales_monthly) AS sales_monthly, SUM(total_sold) AS total_sold, SUM(total_returned) AS total_returned,
((SUM(total_returned)/SUM(total_sold)) * 100) AS returned_rate
FROM
(
SELECT p.products_id, jc.price, jc.price_end_customer AS price_ecp, jc.total_sales, jc.active_age, jc.sales_weekly,
jc.sales_monthly, jc.total_sold, jc.total_returned, jc.price_uvp, p.price_margin, p.vc_percent, p.material_expenses,
p.products_date_added, p.stars , pb.brand_name, p.family_id, p.products_price_default AS price_default, pl.sales_monthly_1,
pl.sales_monthly_2, pl.sales_monthly_3, pl.sold_monthly_1, pl.sold_monthly_2, pl.sold_monthly_3, pst.stock AS hh_stock,
(pst.stock * p.average_stock_value) AS hh_stock_value, pnc.products_length,
IF(ploc.cogs IS NULL OR ploc.cogs=0,
(CASE p.complexity
WHEN 'F' THEN ROUND(5*(p.material_expenses+(7.5/100*p.material_expenses)+1.7+0.25+2.2)/100+(p.material_expenses+(7.5/100*p.material_expenses)+1.7+0.25+2.2),2)
WHEN 'E' THEN ROUND(5*(p.material_expenses+(7.5/100*p.material_expenses)+1.7+0.25+2.2)/100+(p.material_expenses+(7.5/100*p.material_expenses)+1.7+0.25+2.2),2)
WHEN 'N' THEN ROUND(5*(p.material_expenses+(7.5/100*p.material_expenses)+2.4+0.25+2.2)/100+(p.material_expenses+(7.5/100*p.material_expenses)+2.4+0.25+2.2),2)
WHEN 'M' THEN ROUND(5*(p.material_expenses+(7.5/100*p.material_expenses)+2.4+0.25+2.2)/100+(p.material_expenses+(7.5/100*p.material_expenses)+2.4+0.25+2.2),2)
WHEN 'I' THEN ROUND(5*(p.material_expenses+(7.5/100*p.material_expenses)+3.5+0.25+2.2)/100+(p.material_expenses+(7.5/100*p.material_expenses)+3.5+0.25+2.2),2)
WHEN 'H' THEN ROUND(5*(p.material_expenses+(7.5/100*p.material_expenses)+3.5+0.25+2.2)/100+(p.material_expenses+(7.5/100*p.material_expenses)+3.5+0.25+2.2),2)
ELSE ROUND(5*(p.material_expenses+(7.5/100*p.material_expenses)+5+0.25+2.2)/100+(p.material_expenses+(7.5/100*p.material_expenses)+5+0.25+2.2),2) END), ploc.cogs) AS cogs
FROM products p
LEFT JOIN jng_sp_catalog jc ON jc.products_id=p.products_id
LEFT JOIN products_description pd ON pd.products_id = p.products_id AND pd.language_id = 2
LEFT JOIN products_description2 pd2 ON pd2.products_id = p.products_id
LEFT JOIN products_brand pb ON pb.products_brand_id = p.products_brand_id
LEFT JOIN products_log pl ON pl.products_id = p.products_id
LEFT JOIN products_log_static pls ON pls.products_id=p.products_id
LEFT JOIN products_local ploc ON ploc.products_id = p.products_id
LEFT JOIN products_non_configurator pnc ON pnc.products_id = p.products_id
INNER JOIN
(
SELECT shp.products_id, CONCAT(',', GROUP_CONCAT(shp.styles_id), ',') AS styles_id
FROM styles_has_products shp GROUP BY shp.products_id HAVING styles_id NOT LIKE '%,1967,%') subquery_styles ON subquery_styles.products_id = p.products_id
LEFT JOIN products_stock_temp pst ON pst.products_id=p.products_id WHERE p.active_status='1' AND p.categories_top_id = '1') dt GROUP BY products_id ORDER BY products_id;
The result of explain is like this:
+----+-------------+------------+------------+--------+---------------------+-------------+---------+------------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------------+-------------+---------+------------------------------------+--------+----------+----------------------------------------------+
| 1 | PRIMARY | p | NULL | ALL | PRIMARY | NULL | NULL | NULL | 40458 | 1.00 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | pb | NULL | eq_ref | PRIMARY | PRIMARY | 4 | manobo_central.p.products_brand_id | 1 | 100.00 | NULL |
| 1 | PRIMARY | ploc | NULL | eq_ref | PRIMARY | PRIMARY | 4 | manobo_central.p.products_id | 1 | 100.00 | NULL |
| 1 | PRIMARY | pl | NULL | eq_ref | PRIMARY | PRIMARY | 4 | manobo_central.p.products_id | 1 | 100.00 | Using where |
| 1 | PRIMARY | pls | NULL | eq_ref | PRIMARY | PRIMARY | 4 | manobo_central.p.products_id | 1 | 100.00 | Using index |
| 1 | PRIMARY | pst | NULL | eq_ref | PRIMARY | PRIMARY | 4 | manobo_central.p.products_id | 1 | 100.00 | NULL |
| 1 | PRIMARY | pd2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | manobo_central.p.products_id | 1 | 100.00 | Using index |
| 1 | PRIMARY | pnc | NULL | eq_ref | PRIMARY | PRIMARY | 4 | manobo_central.p.products_id | 1 | 100.00 | Using where |
| 1 | PRIMARY | pd | NULL | eq_ref | PRIMARY | PRIMARY | 8 | manobo_central.p.products_id,const | 1 | 100.00 | Using index |
| 1 | PRIMARY | jc | NULL | ref | products_id | products_id | 4 | manobo_central.p.products_id | 4 | 100.00 | Using where |
| 1 | PRIMARY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 4 | manobo_central.p.products_id | 10 | 100.00 | Using where |
| 3 | DERIVED | shp | NULL | index | PRIMARY,products_id | PRIMARY | 8 | NULL | 208226 | 100.00 | Using index; Using filesort |
+----+-------------+------------+------------+--------+---------------------+-------------+---------+------------------------------------+--------+----------+----------------------------------------------+
I have options in mind.
Again, Do you think i need to switch to VIEW? Or just try to optimise again the subquery.
Many Thanks!
EDIT: table products index
create index family_id on products (family_id);
create index idx_products_date_added on products (products_date_added);
create index material_expenses on products (material_expenses);
create index products_brand_id on products (products_brand_id);
create index products_ean on products (products_ean);
create index products_status on products (products_status);
create index tb_status on products (tb_status);
EDIT: table style_has_products
CREATE TABLE `styles_has_products` (
`styles_id` int(10) unsigned NOT NULL DEFAULT '0',
`products_id` int(10) unsigned NOT NULL DEFAULT '0',
`date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`styles_id`,`products_id`),
KEY `products_id` (`products_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Adjust the size and properties of the memory areas that MySQL uses for caching. With efficient use of the InnoDB buffer pool, MyISAM key cache, and the MySQL query cache, repeated queries run faster because the results are retrieved from memory the second and subsequent times.
For multiple-table subqueries, execution of NULL IN (SELECT ...) is particularly slow because the join optimizer does not optimize for the case where the outer expression is NULL .
A Sub-Query Does Not Hurt Performance.
first and foremost never write such a complex query for real time use. i'll suggest do batch process and maintain data warehouse. and use real time query on data warehouse.
still there are many things you should not do to SQL query on real time use to get performance. like never use more join operation, never put more if else conditions , never apply group by especially if table is huge, look for proper index , partition structure in table.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With