SELECT 
  pf.feature_id, 
  pf.company_id, 
  pf.feature_type, 
  pf.parent_id, 
  pf.display_on_product, 
  pf.display_on_catalog, 
  pf.display_on_header, 
  cscart_product_features_descriptions.description, 
  cscart_product_features_descriptions.internal_name, 
  cscart_product_features_descriptions.lang_code, 
  cscart_product_features_descriptions.prefix, 
  cscart_product_features_descriptions.suffix, 
  pf.categories_path, 
  cscart_product_features_descriptions.full_description, 
  pf.status, 
  pf.comparison, 
  pf.position, 
  pf.purpose, 
  pf.feature_style, 
  pf.filter_style, 
  pf.feature_code, 
  pf.timestamp, 
  pf.updated_timestamp, 
  pf_groups.position AS group_position, 
  cscart_product_features_values.value, 
  cscart_product_features_values.variant_id, 
  cscart_product_features_values.value_int 
FROM 
  cscart_product_features AS pf 
  LEFT JOIN cscart_product_features AS pf_groups ON pf.parent_id = pf_groups.feature_id 
  LEFT JOIN cscart_product_features_descriptions AS pf_groups_description ON pf_groups_description.feature_id = pf.parent_id 
  AND pf_groups_description.lang_code = 'tr' 
  LEFT JOIN cscart_product_features_descriptions ON cscart_product_features_descriptions.feature_id = pf.feature_id 
  AND cscart_product_features_descriptions.lang_code = 'tr' 
  INNER JOIN cscart_product_features_values ON cscart_product_features_values.feature_id = pf.feature_id 
  AND cscart_product_features_values.product_id = 1129 
  AND cscart_product_features_values.lang_code = 'tr' 
WHERE 
  1 
  AND pf.feature_type != 'G' 
  AND pf.status IN ('A') 
  AND (
    pf_groups.status IN ('A') 
    OR pf_groups.status IS NULL
  ) 
  AND pf.display_on_product = 'Y' 
  AND (
    pf.categories_path = '' 
    OR ISNULL(pf.categories_path) 
    OR FIND_IN_SET(603, pf.categories_path) 
    OR FIND_IN_SET(557, pf.categories_path) 
    OR FIND_IN_SET(734, pf.categories_path)
  ) 
GROUP BY 
  pf.feature_id 
ORDER BY 
  group_position, 
  pf_groups_description.description, 
  pf_groups.feature_id, 
  pf.position, 
  cscart_product_features_descriptions.description, 
  pf.feature_id

Query time 0.00065

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_product_features_values ref PRIMARY,fl,lang_code,product_id,fpl,idx_product_feature_variant_id idx_product_feature_variant_id 3 const 8 Using index condition; Using temporary; Using filesort
1 SIMPLE pf eq_ref PRIMARY,status PRIMARY 3 hobilifedt.cscart_product_features_values.feature_id 1 Using where
1 SIMPLE pf_groups eq_ref PRIMARY PRIMARY 3 hobilifedt.pf.parent_id 1 Using where
1 SIMPLE pf_groups_description eq_ref PRIMARY PRIMARY 9 hobilifedt.pf.parent_id,const 1 Using where
1 SIMPLE cscart_product_features_descriptions eq_ref PRIMARY PRIMARY 9 hobilifedt.cscart_product_features_values.feature_id,const 1 Using where

Result

feature_id company_id feature_type parent_id display_on_product display_on_catalog display_on_header description internal_name lang_code prefix suffix categories_path full_description status comparison position purpose feature_style filter_style feature_code timestamp updated_timestamp group_position value variant_id value_int
550 0 E 0 Y Y Y Marka Marka tr A N 0 organize_catalog brand checkbox 1698890376 1717032660 1226 0.00
551 0 S 553 Y Y Y Araç Marka Araç Marka tr A N 0 find_products text checkbox 1711773057 1717032905 0 1215
552 0 M 553 Y Y Y Araç Model Araç Model tr A N 0 find_products multiple_checkbox checkbox 1711773075 1717032942 0 1216
47 0 C 20 Y Y N 3D support 3D support tr A N 0 find_products checkbox checkbox 0 0 10 N 0
22 0 C 20 Y Y N HD support HD support tr A N 0 find_products checkbox checkbox 0 0 10 N 0
34 0 C 33 Y Y N Video playback Video playback tr A N 0 find_products checkbox checkbox 0 0 30 N 0