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 = 8295 
  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 = 2 
    OR cscart_seo_names.company_id = 0
  ) 
WHERE 
  1 
  AND cscart_product_feature_variants.feature_id IN (176) 
GROUP BY 
  cscart_product_feature_variants.variant_id 
ORDER BY 
  cscart_product_feature_variants.position, 
  cscart_product_feature_variant_descriptions.variant

Query time 0.00474

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 26 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_idx,variant_id_idx,product_feature_idx product_id 3 const 96 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
6259 VELUX en 176 10 6259 M
6260 FAKRO en 176 20 6260 M
6261 Roto en 176 30 6261 M
6262 OptiLight en 176 40 6262 M
6270 Aron en 176 45 6270 M
6271 Artens en 176 46 6271 M
6263 Skylight en 176 50 6263 M
6264 Dakea en 176 60 6264 M
6265 Dakstra en 176 70 6265 M
6266 RoofLITE+ en 176 80 6266 M
6267 Solstro en 176 90 6267 M
6268 Luxtra en 176 100 6268 M
6269 Luminatec en 176 110 6269 M
6272 Balio en 176 120 6272 M
6298 Skylight Premium en 176 130 M
6299 Skylight SUPRO en 176 140 M