Sandbox

SELECT 
  v.feature_id, 
  v.value, 
  v.value_int, 
  v.variant_id, 
  f.feature_type, 
  fd.internal_name, 
  fd.description, 
  fd.prefix, 
  fd.suffix, 
  vd.variant, 
  f.parent_id, 
  f.position, 
  gf.position as gposition, 
  f.feature_style as feature_style, 
  f.display_on_header, 
  f.display_on_catalog, 
  f.display_on_product, 
  f.feature_code, 
  f.purpose 
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' 
WHERE 
  f.status IN ('A') 
  AND v.product_id = 66725 
  AND (
    f.categories_path = '' 
    OR FIND_IN_SET(1677, f.categories_path) 
    OR FIND_IN_SET(2289, f.categories_path) 
    OR FIND_IN_SET(2331, f.categories_path) 
    OR FIND_IN_SET(2685, f.categories_path) 
    OR FIND_IN_SET(2827, f.categories_path) 
    OR FIND_IN_SET(2828, 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.internal_name, 
  fv.position

Query time 0.00097

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "17.07"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "v",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "variant_id",
              "lang_code",
              "product_id",
              "fpl",
              "idx_product_feature_variant_id",
              "fl"
            ],
            "key": "product_id",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 10,
            "rows_produced_per_join": 4,
            "filtered": "50.00",
            "index_condition": "((`cscart_db`.`v`.`lang_code` = 'en') and (`cscart_db`.`v`.`feature_id` is not null))",
            "cost_info": {
              "read_cost": "7.74",
              "eval_cost": "0.50",
              "prefix_cost": "8.74",
              "data_read_per_join": "199"
            },
            "used_columns": [
              "feature_id",
              "product_id",
              "variant_id",
              "value",
              "value_int",
              "lang_code"
            ]
          }
        },
        {
          "table": {
            "table_name": "f",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "status"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "feature_id"
            ],
            "key_length": "3",
            "ref": [
              "cscart_db.v.feature_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "86.54",
            "cost_info": {
              "read_cost": "1.25",
              "eval_cost": "0.43",
              "prefix_cost": "10.49",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "feature_id",
              "feature_code",
              "purpose",
              "feature_style",
              "feature_type",
              "categories_path",
              "parent_id",
              "display_on_product",
              "display_on_catalog",
              "display_on_header",
              "status",
              "position"
            ],
            "attached_condition": "((`cscart_db`.`f`.`status` = 'A') and ((`cscart_db`.`f`.`categories_path` = '') or (0 <> find_in_set(1677,`cscart_db`.`f`.`categories_path`)) or (0 <> find_in_set(2289,`cscart_db`.`f`.`categories_path`)) or (0 <> find_in_set(2331,`cscart_db`.`f`.`categories_path`)) or (0 <> find_in_set(2685,`cscart_db`.`f`.`categories_path`)) or (0 <> find_in_set(2827,`cscart_db`.`f`.`categories_path`)) or (0 <> find_in_set(2828,`cscart_db`.`f`.`categories_path`))) and (if(`cscart_db`.`f`.`parent_id`,(/* select#2 */ select `cscart_db`.`df`.`status` from `cscart_db`.`cscart_product_features` `df` where (`cscart_db`.`df`.`feature_id` = `cscart_db`.`f`.`parent_id`)),'A') = 'A') and ((`cscart_db`.`v`.`variant_id` <> 0) or ((`cscart_db`.`f`.`feature_type` <> 'C') and (`cscart_db`.`v`.`value` <> '')) or (`cscart_db`.`f`.`feature_type` = 'C') or (`cscart_db`.`v`.`value_int` <> 0)))",
            "attached_subqueries": [
              {
                "dependent": true,
                "cacheable": false,
                "query_block": {
                  "select_id": 2,
                  "cost_info": {
                    "query_cost": "0.35"
                  },
                  "table": {
                    "table_name": "df",
                    "access_type": "eq_ref",
                    "possible_keys": [
                      "PRIMARY"
                    ],
                    "key": "PRIMARY",
                    "used_key_parts": [
                      "feature_id"
                    ],
                    "key_length": "3",
                    "ref": [
                      "cscart_db.f.parent_id"
                    ],
                    "rows_examined_per_scan": 1,
                    "rows_produced_per_join": 1,
                    "filtered": "100.00",
                    "cost_info": {
                      "read_cost": "0.25",
                      "eval_cost": "0.10",
                      "prefix_cost": "0.35",
                      "data_read_per_join": "448"
                    },
                    "used_columns": [
                      "feature_id",
                      "status"
                    ]
                  }
                }
              }
            ]
          }
        },
        {
          "table": {
            "table_name": "fd",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "feature_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "cscart_db.v.feature_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.08",
              "eval_cost": "0.43",
              "prefix_cost": "12.00",
              "data_read_per_join": "9K"
            },
            "used_columns": [
              "feature_id",
              "description",
              "prefix",
              "suffix",
              "lang_code",
              "internal_name"
            ]
          }
        },
        {
          "table": {
            "table_name": "fv",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "variant_feature_idx",
              "idx_var_feat_pos"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id"
            ],
            "key_length": "3",
            "ref": [
              "cscart_db.v.variant_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.08",
              "eval_cost": "0.43",
              "prefix_cost": "13.52",
              "data_read_per_join": "4K"
            },
            "used_columns": [
              "variant_id",
              "position"
            ]
          }
        },
        {
          "table": {
            "table_name": "vd",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "cscart_db.fv.variant_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.60",
              "eval_cost": "0.43",
              "prefix_cost": "15.55",
              "data_read_per_join": "32K"
            },
            "used_columns": [
              "variant_id",
              "variant",
              "lang_code"
            ]
          }
        },
        {
          "table": {
            "table_name": "gf",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "feature_id"
            ],
            "key_length": "3",
            "ref": [
              "cscart_db.f.parent_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1.08",
              "eval_cost": "0.43",
              "prefix_cost": "17.07",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "feature_id",
              "feature_type",
              "position"
            ],
            "attached_condition": "<if>(is_not_null_compl(gf), (`cscart_db`.`gf`.`feature_type` = 'G'), true)"
          }
        }
      ]
    }
  }
}

Result

feature_id	value	variant_id	feature_type	internal_name	description	variant	parent_id	position	gposition	feature_style	display_on_header	display_on_catalog	display_on_product	purpose
175	Water, Dipropylene Glycol, Glycerin, Hamamelis Virginiana (Witch Hazel) Extract, Butylene Glycol,1,2-Hexanediol, Arginine,Carbomer, Caprylyl Glycol, Betaine, Allantoin, Panthenol, Camellia Sinensis Leaf Extract, Rosmarinus Officinalis (Rosemary) Leaf Extr	0	T	All Ingredients	All Ingredients		66	40	0	text	N	N	Y	describe_product
162		62081	M	Awards and certifications	Awards and certifications	Gender Neutral	0	0		multiple_checkbox	N	N	N	find_products
62		59745	E	Brand	Brand	Tirtir	0	1		brand	Y	N	Y	organize_catalog
63		10351	S	Country of Origin	Country of Origin	South Korea	66	100	0	text	N	N	Y	find_products
72	Apply a few drops of TirTir SOS Serum right after cleansing your skin to reveal your skin's natural radiance and to tightly lock in moisture, eliminating any sense of tightness.Take an appropriate amount onto the palm, gently pat on face and let the produ	0	T	How to Use	How to Use		0	3		text	N	N	N	describe_product
65		59655	M	Key Ingredients	Key Ingredients	Water, Dipropylene Glycol, Glycerin	0	30		multiple_checkbox	N	N	Y	find_products
163		54763	M	Tags	Tags	k-beauty tag	0	0		multiple_checkbox	N	N	N	find_products