Report which shows all products and its attributes, features and categories it belongs to

[pradeepjain]

I am trying to build a report which shows all products and its attributes, features and categories it belongs to. But query i wrote is taking lot of time. how do i improve this ?

 

SELECT   max(if((ps_attribute_group_lang.name like "Color"),ps_attribute_lang.name,'')) AS Color, max(if((ps_attribute_group_lang.name like "Size"),ps_attribute_lang.name,'')) AS Size, max(if((ps_feature_lang.name like "Brand"),ps_feature_value_lang.value,'')) AS Brand, max(if((ps_feature_lang.name like "Sub brand"),ps_feature_value_lang.value,'')) AS Subbrand, max(if((ps_feature_lang.name like "Occasion"),ps_feature_value_lang.value,'')) AS Occasion, max(if((ps_feature_lang.name like "Sleeves"),ps_feature_value_lang.value,'')) AS Sleeves, max(if((ps_feature_lang.name like "Cuffs"),ps_feature_value_lang.value,'')) AS Cuffs, max(if((ps_feature_lang.name like "Collar"),ps_feature_value_lang.value,'')) AS Collar, max(if((ps_feature_lang.name like "Neck"),ps_feature_value_lang.value,'')) AS Neck, max(if((ps_feature_lang.name like "Hood"),ps_feature_value_lang.value,'')) AS Hood, max(if((ps_feature_lang.name like "Shoulder"),ps_feature_value_lang.value,'')) AS Shoulder, max(if((ps_feature_lang.name like "Straps"),ps_feature_value_lang.value,'')) AS Straps, max(if((ps_feature_lang.name like "Trouser Front"),ps_feature_value_lang.value,'')) AS TrouserFront, max(if((ps_feature_lang.name like "Front Opening"),ps_feature_value_lang.value,'')) AS FrontOpening, max(if((ps_feature_lang.name like "Suit Front"),ps_feature_value_lang.value,'')) AS SuitFront, max(if((ps_feature_lang.name like "Fit"),ps_feature_value_lang.value,'')) AS Fit, max(if((ps_feature_lang.name like "Wash"),ps_feature_value_lang.value,'')) AS Wash, max(if((ps_feature_lang.name like "Length"),ps_feature_value_lang.value,'')) AS Length, max(if((ps_feature_lang.name like "Style"),ps_feature_value_lang.value,'')) AS Style, max(if((ps_feature_lang.name like "Pattern"),ps_feature_value_lang.value,'')) AS Pattern, max(if((ps_feature_lang.name like "Weatherproof"),ps_feature_value_lang.value,'')) AS Weatherproof, max(if((ps_feature_lang.name like "Fabric Type"),ps_feature_value_lang.value,'')) AS FabricType, max(if((ps_feature_lang.name like "Suit Type"),ps_feature_value_lang.value,'')) AS SuitType, max(if((ps_feature_lang.name like "Accessories"),ps_feature_value_lang.value,'')) AS Accessories, max(if((ps_feature_lang.name like "Fashion Accessories"),ps_feature_value_lang.value,'')) AS FashionAccessories, max(if((ps_feature_lang.name like "Shoe Type"),ps_feature_value_lang.value,'')) AS ShoeType, max(if((ps_feature_lang.name like "Bag Type"),ps_feature_value_lang.value,'')) AS BagType, max(if((ps_feature_lang.name like "Material"),ps_feature_value_lang.value,'')) AS Material, max(if((ps_feature_lang.name like "Ethnic Bottom Style "),ps_feature_value_lang.value,'')) AS EthnicBottomStyle,  ps_product_attribute.id_product AS productid,
`ps_product_lang`.`name` AS `productname`,
`ps_product_attribute`.`ean13`		 AS `a_ean13`,
`ps_category_lang`.`name`			  AS `categoryname`,
`ps_product_attribute`.`id_product_attribute`  AS `id_product_attribute`,
`t_vendorproducts`.`a_vendorid`	    AS `vendorid`,
round(`ps_product`.`price`,2)  AS price   from
    (((((((`ps_product_attribute`
    join `ps_product_lang`		  ON ((`ps_product_lang`.`id_product` = `ps_product_attribute`.`id_product`)))
    join `ps_product_attribute_combination` ON ((`ps_product_attribute_combination`.`id_product_attribute` = `ps_product_attribute`.`id_product_attribute`)))
    join `ps_attribute`		    ON ((`ps_attribute`.`id_attribute` = `ps_product_attribute_combination`.`id_attribute`)))
    join `ps_attribute_lang`	    ON ((`ps_attribute_lang`.`id_attribute` = `ps_product_attribute_combination`.`id_attribute`)))
    join `ps_attribute_group_lang`   ON (((`ps_attribute`.`id_attribute_group` = `ps_attribute_group_lang`.`id_attribute_group`))))))
    join  ps_feature_product	    on ps_feature_product.id_product = ps_product_attribute.id_product
   join  ps_feature_value_lang	  on ps_feature_value_lang.id_feature_value = ps_feature_product.id_feature_value
    join  ps_feature_lang		   on ps_feature_product.id_feature = ps_feature_lang.id_feature
    join  ps_product			   on ps_product.id_product = ps_product_attribute.id_product
   join  ps_category  cate1	   on cate1.id_category = ps_product.id_category_default
   join  ps_category  cate2	   on cate2.id_category = cate1.id_parent
   join  ps_category_lang	    on ps_category_lang.id_category = cate1.id_category
  WHERE  ps_product_lang.id_shop = 1  GROUP BY ps_product_attribute.ean13 ORDER BY productid