SELECT 
  cscart_product_feature_variant_descriptions.*, 
  cscart_product_feature_variants.*, 
  cscart_product_features_values.variant_id as selected, 
  cscart_product_features.feature_type, 
  cscart_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path 
FROM 
  cscart_product_feature_variants 
  LEFT JOIN cscart_product_feature_variant_descriptions ON cscart_product_feature_variant_descriptions.variant_id = cscart_product_feature_variants.variant_id 
  AND cscart_product_feature_variant_descriptions.lang_code = 'en' 
  LEFT JOIN cscart_product_features_values ON cscart_product_features_values.variant_id = cscart_product_feature_variants.variant_id 
  AND cscart_product_features_values.lang_code = 'en' 
  AND cscart_product_features_values.product_id = 672 
  LEFT JOIN cscart_product_features ON cscart_product_features.feature_id = cscart_product_feature_variants.feature_id 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = cscart_product_feature_variants.variant_id 
  AND cscart_seo_names.type = 'e' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'en' 
  AND (
    cscart_seo_names.company_id = 1 
    OR cscart_seo_names.company_id = 0
  ) 
WHERE 
  1 
  AND cscart_product_feature_variants.feature_id IN (30) 
GROUP BY 
  cscart_product_feature_variants.variant_id 
ORDER BY 
  cscart_product_feature_variants.position, 
  cscart_product_feature_variant_descriptions.variant

Query time 0.00411

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_product_feature_variants ref feature_id feature_id 3 const 30 Using where; Using temporary; Using filesort
1 SIMPLE cscart_product_feature_variant_descriptions eq_ref PRIMARY PRIMARY 9 mahm3t_cs443.cscart_product_feature_variants.variant_id,const 1 Using where
1 SIMPLE cscart_product_features_values ref variant_id,lang_code,product_id product_id 3 const 22 Using where
1 SIMPLE cscart_product_features const PRIMARY PRIMARY 3 const 1
1 SIMPLE cscart_seo_names ref PRIMARY,dispatch PRIMARY 206 mahm3t_cs443.cscart_product_feature_variants.variant_id,const,const,const 2 Using where

Result

variant_id variant description page_title meta_keywords meta_description lang_code custom_header feature_id url position selected feature_type seo_name seo_path
6243 Eurocolor en 30 0 E eurocolor
6288 Flexible panel en 30 0 E flexible-panel
6289 Flexible panel en 30 0 E flexible-panel-en
6300 Gealan en 30 0 E gealan-en
6278 XELLA en 30 0 E xella-en
128 FAKRO en 30 10 E fakro-en
130 OptiLight (FAKRO Gruppe) en 30 20 E optilight-en
125 Rockwool en 30 20 E rockwool-en
5891 KNAUF en 30 30 E knauf-en
129 SkyLight en 30 30 E skylight-en
240 VELUX en 30 40 E velux-en
6131 RoofLITE+ en 30 rooflite 42 E rooflite-en
5870 YTONG en 30 45 E ytong-en
126 URSA en 30 50 E ursa-en
6124 Roto en 30 55 E roto
5771 CREATON en 30 60 E creaton-en-en
5890 Stegu en 30 65 E stegu-en
5893 Kronospan en 30 80 E kronospan-en
5773 SOLBET <p style="text-align: justify;">SOLBET is a leading Polish manufacturer of cellular concrete. The Group’s market share as far as the sales of autoclaved cellular concrete is concerned accounts for 33%. Currently, manufacturing capacity of the Group’s all plants amounts to 2 million cubic meters of this advanced material per year.&nbsp;<span style="line-height: 1.6;">SOLBET have fabricated cellular concrete wall elements since 1954 allowing us to gain extensive experience in the field. Presently, manufacture of such wall materials is carried out in our five factories in with the main one in Solec Kujawski, in a plant with the largest daily output in all Europe.&nbsp;As well as wall material production plants, SOLBET Group owns facilities manufacturing process lines for fabrication of cellular concrete products. This makes us technologically independent and enables our plants to be modernised offering highest quality and the most advanced materials.</span></p> <p>&nbsp;</p> en 30 90 E solbet-en
123 Atlas en 30 100 123 E atlas-en
212 TermoOrganika en 30 100 E termoorganika-en
6223 Veka en 30 110 E veka
6221 REHAU en 30 120 E rehau
6222 Schüco en 30 130 E schuco
5936 Traders en 30 170 E baubay-en
5937 Koelner en 30 180 E koelner-en
5933 mdm en 30 190 E mdm-en
5935 Portos en 30 200 E portos-en
5892 Siniat en 30 210 E siniat-en
5938 SOUDAL en 30 220 E soudal-en
5932 vetrex en 30 230 E vetrex-en
5894 POZBUD en 30 260 E pol-skone-en
158 Barlinek en 30 320 E barlinek-en