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.
- I will drop subquery and use VIEWS to output the data just like using query. Because i have subquery in FROM, so i will use VIEWS from VIEWS. But some said it will affected in performances. How you guys think about this?
- I will still using subquery, but will try and search how to optimize the query. For this one, i wanted to ask you guys, for the first result row in EXPLAIN TABLE, It shows table production p which the type 'all', how to avoid 'all' ? I've managed to use type 'eq_ref' for others table, but still have no clue why the product table is 'all'?
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