SELECT 
  f.feature_id, 
  f.purpose, 
  p.product_id, 
  p.parent_product_id, 
  g.id, 
  g.code 
FROM 
  cscart_product_variation_group_features AS f 
  INNER JOIN cscart_product_variation_groups AS g ON f.group_id = g.id 
  INNER JOIN cscart_product_variation_group_products AS p ON f.group_id = p.group_id 
WHERE 
  p.product_id IN (
    4539, 4359, 4035, 4324, 4657, 4360, 4548, 
    4662, 4062, 4677, 4505, 4046, 4076, 
    4242, 4829, 4068, 4095, 4469, 4112, 
    4509, 4702, 4665, 4050, 4259, 4330, 
    4506, 4266, 4409, 4691, 4697, 4338, 
    4408, 4521, 4692, 4407, 4381, 4523, 
    4241, 4274, 4507, 4520, 4361, 4676, 
    4061, 4413, 4237, 4538, 4512, 4537, 
    4658, 4540
  )

Query time 0.00181

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "71.41"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "p",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "rows_examined_per_scan": 51,
          "rows_produced_per_join": 51,
          "filtered": "100.00",
          "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`p`.`product_id` in (4539,4359,4035,4324,4657,4360,4548,4662,4062,4677,4505,4046,4076,4242,4829,4068,4095,4469,4112,4509,4702,4665,4050,4259,4330,4506,4266,4409,4691,4697,4338,4408,4521,4692,4407,4381,4523,4241,4274,4507,4520,4361,4676,4061,4413,4237,4538,4512,4537,4658,4540))",
          "cost_info": {
            "read_cost": "30.61",
            "eval_cost": "5.10",
            "prefix_cost": "35.71",
            "data_read_per_join": "816"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "g",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "3",
          "ref": [
            "softwarepirmam_hewadelivard_cscart_4.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 51,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "12.75",
            "eval_cost": "5.10",
            "prefix_cost": "53.56",
            "data_read_per_join": "19K"
          },
          "used_columns": [
            "id",
            "code"
          ]
        }
      },
      {
        "table": {
          "table_name": "f",
          "access_type": "ref",
          "possible_keys": [
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "softwarepirmam_hewadelivard_cscart_4.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 51,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "12.75",
            "eval_cost": "5.10",
            "prefix_cost": "71.41",
            "data_read_per_join": "5K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}