SELECT 
  v.product_id, 
  v.feature_id, 
  v.value, 
  v.value_int, 
  v.variant_id, 
  f.feature_type, 
  fd.description, 
  fd.prefix, 
  fd.suffix, 
  vd.variant, 
  f.parent_id, 
  f.position, 
  gf.position as gposition 
FROM 
  cscart_product_features as f 
  LEFT JOIN cscart_product_features_values as v ON v.feature_id = f.feature_id 
  LEFT JOIN cscart_product_features_descriptions as fd ON fd.feature_id = v.feature_id 
  AND fd.lang_code = 'en' 
  LEFT JOIN cscart_product_feature_variants fv ON fv.variant_id = v.variant_id 
  LEFT JOIN cscart_product_feature_variant_descriptions as vd ON vd.variant_id = fv.variant_id 
  AND vd.lang_code = 'en' 
  LEFT JOIN cscart_product_features as gf ON gf.feature_id = f.parent_id 
  AND gf.feature_type = 'G' 
  INNER JOIN cscart_ult_objects_sharing ON (
    cscart_ult_objects_sharing.share_object_id = f.feature_id 
    AND cscart_ult_objects_sharing.share_company_id = 1 
    AND cscart_ult_objects_sharing.share_object_type = 'product_features'
  ) 
WHERE 
  f.status IN ('A') 
  AND v.product_id in (1529) 
  AND f.display_on_catalog = 'Y' 
  AND (
    f.categories_path = '' 
    OR FIND_IN_SET(329, f.categories_path) 
    OR FIND_IN_SET(361, f.categories_path) 
    OR FIND_IN_SET(362, f.categories_path) 
    OR FIND_IN_SET(363, f.categories_path) 
    OR FIND_IN_SET(328, f.categories_path) 
    OR FIND_IN_SET(335, f.categories_path) 
    OR FIND_IN_SET(353, f.categories_path) 
    OR FIND_IN_SET(352, f.categories_path) 
    OR FIND_IN_SET(350, f.categories_path) 
    OR FIND_IN_SET(351, f.categories_path) 
    OR FIND_IN_SET(336, f.categories_path) 
    OR FIND_IN_SET(345, f.categories_path) 
    OR FIND_IN_SET(347, f.categories_path) 
    OR FIND_IN_SET(344, f.categories_path) 
    OR FIND_IN_SET(339, f.categories_path) 
    OR FIND_IN_SET(340, f.categories_path) 
    OR FIND_IN_SET(341, f.categories_path) 
    OR FIND_IN_SET(342, f.categories_path) 
    OR FIND_IN_SET(338, f.categories_path) 
    OR FIND_IN_SET(337, f.categories_path) 
    OR FIND_IN_SET(349, f.categories_path) 
    OR FIND_IN_SET(343, f.categories_path) 
    OR FIND_IN_SET(360, f.categories_path) 
    OR FIND_IN_SET(359, f.categories_path) 
    OR FIND_IN_SET(354, f.categories_path) 
    OR FIND_IN_SET(355, f.categories_path) 
    OR FIND_IN_SET(356, f.categories_path) 
    OR FIND_IN_SET(357, f.categories_path) 
    OR FIND_IN_SET(358, f.categories_path) 
    OR FIND_IN_SET(330, f.categories_path) 
    OR FIND_IN_SET(378, f.categories_path) 
    OR FIND_IN_SET(377, f.categories_path) 
    OR FIND_IN_SET(376, f.categories_path) 
    OR FIND_IN_SET(375, f.categories_path) 
    OR FIND_IN_SET(374, f.categories_path) 
    OR FIND_IN_SET(373, f.categories_path) 
    OR FIND_IN_SET(372, f.categories_path) 
    OR FIND_IN_SET(371, f.categories_path) 
    OR FIND_IN_SET(370, f.categories_path) 
    OR FIND_IN_SET(369, f.categories_path) 
    OR FIND_IN_SET(368, f.categories_path) 
    OR FIND_IN_SET(331, f.categories_path) 
    OR FIND_IN_SET(379, f.categories_path) 
    OR FIND_IN_SET(380, f.categories_path) 
    OR FIND_IN_SET(381, f.categories_path) 
    OR FIND_IN_SET(384, f.categories_path) 
    OR FIND_IN_SET(385, f.categories_path) 
    OR FIND_IN_SET(386, f.categories_path) 
    OR FIND_IN_SET(332, f.categories_path) 
    OR FIND_IN_SET(388, f.categories_path) 
    OR FIND_IN_SET(390, f.categories_path) 
    OR FIND_IN_SET(391, f.categories_path) 
    OR FIND_IN_SET(392, f.categories_path) 
    OR FIND_IN_SET(393, f.categories_path) 
    OR FIND_IN_SET(394, f.categories_path) 
    OR FIND_IN_SET(395, f.categories_path) 
    OR FIND_IN_SET(396, f.categories_path)
  ) 
  AND IF(
    f.parent_id, 
    (
      SELECT 
        status 
      FROM 
        cscart_product_features as df 
      WHERE 
        df.feature_id = f.parent_id
    ), 
    'A'
  ) IN ('A') 
  AND (
    v.variant_id != 0 
    OR (
      f.feature_type != 'C' 
      AND v.value != ''
    ) 
    OR (f.feature_type = 'C') 
    OR v.value_int != ''
  ) 
  AND v.lang_code = 'en' 
ORDER BY 
  fd.description, 
  fv.position

Query time 0.00189

JSON explain

{
  "query_block": {
    "select_id": 1,
    "message": "Impossible WHERE noticed after reading const tables",
    "optimized_away_subqueries": [
      {
        "dependent": true,
        "cacheable": false,
        "query_block": {
          "select_id": 2,
          "message": "Impossible WHERE noticed after reading const tables"
        }
      }
    ]
  }
}