Sandbox

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 = 'ar' 
  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 = 'ar' 
  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 in (6257, 6259, 6226) 
  AND f.display_on_catalog = 'Y' 
  AND (
    f.categories_path = '' 
    OR FIND_IN_SET(521, f.categories_path) 
    OR FIND_IN_SET(522, f.categories_path) 
    OR FIND_IN_SET(537, 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 = 'ar' 
ORDER BY 
  fd.description, 
  fv.position

Query time 0.00276

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "66.33"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "v",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "fl",
              "variant_id",
              "lang_code",
              "product_id",
              "fpl",
              "idx_product_feature_variant_id"
            ],
            "key": "product_id",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 108,
            "rows_produced_per_join": 35,
            "filtered": "32.51",
            "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`v`.`product_id` in (6257,6259,6226))",
            "cost_info": {
              "read_cost": "45.85",
              "eval_cost": "3.51",
              "prefix_cost": "49.36",
              "data_read_per_join": "27K"
            },
            "used_columns": [
              "feature_id",
              "product_id",
              "variant_id",
              "value",
              "value_int",
              "lang_code"
            ],
            "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`v`.`lang_code` = 'ar') and (`softwarepirmam_hewadelivard_cscart_4`.`v`.`feature_id` is not null))"
          }
        },
        {
          "table": {
            "table_name": "f",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "status"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "feature_id"
            ],
            "key_length": "3",
            "ref": [
              "softwarepirmam_hewadelivard_cscart_4.v.feature_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 3,
            "filtered": "9.53",
            "cost_info": {
              "read_cost": "8.78",
              "eval_cost": "0.33",
              "prefix_cost": "61.65",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "feature_id",
              "feature_type",
              "categories_path",
              "parent_id",
              "display_on_catalog",
              "status",
              "position"
            ],
            "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`f`.`status` = 'A') and (`softwarepirmam_hewadelivard_cscart_4`.`f`.`display_on_catalog` = 'Y') and ((`softwarepirmam_hewadelivard_cscart_4`.`f`.`categories_path` = '') or (0 <> find_in_set(521,`softwarepirmam_hewadelivard_cscart_4`.`f`.`categories_path`)) or (0 <> find_in_set(522,`softwarepirmam_hewadelivard_cscart_4`.`f`.`categories_path`)) or (0 <> find_in_set(537,`softwarepirmam_hewadelivard_cscart_4`.`f`.`categories_path`))) and (if(`softwarepirmam_hewadelivard_cscart_4`.`f`.`parent_id`,(/* select#2 */ select `softwarepirmam_hewadelivard_cscart_4`.`df`.`status` from `softwarepirmam_hewadelivard_cscart_4`.`cscart_product_features` `df` where (`softwarepirmam_hewadelivard_cscart_4`.`df`.`feature_id` = `softwarepirmam_hewadelivard_cscart_4`.`f`.`parent_id`)),'A') = 'A') and ((`softwarepirmam_hewadelivard_cscart_4`.`v`.`variant_id` <> 0) or ((`softwarepirmam_hewadelivard_cscart_4`.`f`.`feature_type` <> 'C') and (`softwarepirmam_hewadelivard_cscart_4`.`v`.`value` <> '')) or (`softwarepirmam_hewadelivard_cscart_4`.`f`.`feature_type` = 'C') or (`softwarepirmam_hewadelivard_cscart_4`.`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": [
                      "softwarepirmam_hewadelivard_cscart_4.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": "440"
                    },
                    "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": [
              "softwarepirmam_hewadelivard_cscart_4.v.feature_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 3,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.84",
              "eval_cost": "0.33",
              "prefix_cost": "62.82",
              "data_read_per_join": "7K"
            },
            "used_columns": [
              "feature_id",
              "description",
              "prefix",
              "suffix",
              "lang_code"
            ]
          }
        },
        {
          "table": {
            "table_name": "fv",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "variant_id"
            ],
            "key_length": "3",
            "ref": [
              "softwarepirmam_hewadelivard_cscart_4.v.variant_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 3,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.84",
              "eval_cost": "0.33",
              "prefix_cost": "63.99",
              "data_read_per_join": "3K"
            },
            "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": [
              "softwarepirmam_hewadelivard_cscart_4.fv.variant_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 3,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.84",
              "eval_cost": "0.33",
              "prefix_cost": "65.16",
              "data_read_per_join": "10K"
            },
            "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": [
              "softwarepirmam_hewadelivard_cscart_4.f.parent_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 3,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.84",
              "eval_cost": "0.33",
              "prefix_cost": "66.33",
              "data_read_per_join": "1K"
            },
            "used_columns": [
              "feature_id",
              "feature_type",
              "position"
            ],
            "attached_condition": "<if>(is_not_null_compl(gf), (`softwarepirmam_hewadelivard_cscart_4`.`gf`.`feature_type` = 'G'), true)"
          }
        }
      ]
    }
  }
}

Result

product_id	feature_id	value_int	variant_id	feature_type	description	variant	position
6226	745		7757	S	Camera Connectivity	Wi-Fi 2.4 GHz (IEEE 802.11 b/g/n)	0
6259	745		8011	S	Camera Connectivity	Wi-Fi IEEE 802.11 b/g/n (2.4 GHz, 20 MHz), supports AP pairing with WPA/WPA2 encryption; also includes RJ-45 Ethernet port (10/100 Mbps auto-adaptive).	0
6257	745		7982	S	Camera Connectivity	Wi-Fi (2.4 GHz IEEE 802.11 b/g/n) + RJ-45 Ethernet port	0
6257	744		7983	S	Camera Type	Outdoor motorized pan & tilt camera, made for full-area external surveillance	0
6259	744		8012	S	Camera Type	Indoor/Outdoor Pan & Tilt camera – compact weather-resistant design offering wide or full 360° coverage	0
6226	744		7878	S	Camera Type	Indoor Pan & Tilt Smart Home Camera	0
6259	735		8013	S	Field of View (FOV)	91° horizontal / 108° diagonal with 4 mm lens, or 56° horizontal / 66° diagonal with 6 mm lens	0
6226	735		7879	S	Field of View (FOV)	~85° (diagonal)	0
6257	735		7984	S	Field of View (FOV)	~91° horizontal (diagonal ~104°) with 4 mm lens; pan range 350°, tilt 80°	0
6259	736		8014	S	Focal Length	4 mm @ F1.6 or 6 mm @ F1.6	0
6257	736		7795	S	Focal Length	4 mm @ F1.6	0
6226	736		7880	S	Focal Length	Fixed lens ≈ 4 mm (F2.4)	0
6226	737		7881	S	Night Mode	Infrared (IR) night vision up to 10 m	0
6259	737		8015	S	Night Mode	0.5 Lux @ F1.6 (min. illumination) and 0 Lux with IR; black-and-white night vision up to 30 m (98 ft); built-in spotlights enable color night mode	0
6257	737		7985	S	Night Mode	Color night vision with built-in spotlights + IR (0 Lux with IR on), range up to ~30 m	0
6226	738		7882	S	Operating Conditions	–10 °C to +45 °C, ≤ 95% humidity	0
6259	738		8016	S	Operating Conditions	–30 °C to +50 °C (–22 °F to 122 °F); humidity ≤ 95 % non-condensing	0
6257	738		7986	S	Operating Conditions	–30 °C to +50 °C, humidity ≤ 95% (non-condensing), weatherproof body (IP65/67)	0
6257	645	0.00	7987	S	Ports	microSD card slot (up to 512 GB) + Ethernet (10/100 Mbps)	110
6259	645	0.00	8018	S	Ports	MicroSD card slot (up to 512 GB), RJ-45 Ethernet port, Wi-Fi antenna	110
6226	645	0.00	7884	S	Ports	MicroSD slot (up to 256 GB), micro USB power	110
6257	642		7702	S	Resolution	4K Ultra HD (3840 × 2160)	50
6259	642		8017	S	Resolution	3K (2880 × 1620 pixels) delivering ultra-sharp video clarity	50
6226	642		7883	S	Resolution	Full HD 1080p (1920×1080)	50
6259	646	1.00	8019	S	Sensor	1/2.7-inch Progressive Scan CMOS sensor with AI human and vehicle shape detection and auto-tracking	130
6257	646	1.00	7988	S	Sensor	1/2.7″ Progressive Scan CMOS, tracks human & vehicle shapes via AI detection	130
6226	646	0.00	7885	S	Sensor	AI human shape and motion detection with auto-tracking and adjustable detection zones.	130
6257	648	0.00	7989	S	التخزين	Local microSD (up to 512 GB) + EZVIZ CloudPlay (cloud backup)	170
6259	648	0.00	8020	S	التخزين	Local MicroSD card support (up to 512 GB) and optional EZVIZ CloudPlay cloud storage	170
6226	648	0.00	7886	S	التخزين	MicroSD (up to 256 GB) + EZVIZ Cloud storage option	170
6226	656		6283	S	لون	Off-White	160
6257	656		1559	S	لون	White	160
6259	656		1559	S	لون	White	160
6257	657		6402	E	ماركة	Ezviz	0
6259	657		6402	E	ماركة	Ezviz	0
6226	657		6402	E	ماركة	Ezviz	0