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 (
    6201, 6202, 6203, 6204, 6205, 6206, 6207, 
    6208, 3033, 3034, 3035, 3036, 3857, 
    3858, 3859, 3860, 2715, 2716, 2717, 
    2718, 2719, 6303, 6304, 6305, 6306, 
    1500, 1501, 1502, 1503, 3726, 5206, 
    5207, 5208, 5209, 6736, 1490, 1491, 
    1492, 1493, 3724, 5198, 5199, 5200, 
    5201, 6735, 1495, 1496, 1497, 1498, 
    3164, 3725, 5202, 5203, 5204, 5205, 
    1485, 1486, 1487, 1488, 3163, 3723, 
    5194, 5195, 5196, 5197, 1429, 1430, 
    1431, 1432, 3161, 3719, 3720, 5142, 
    5143, 5144, 6730, 6731, 3158, 3159, 
    3160, 2102, 2039, 1985, 1472, 1484, 
    5613, 1428, 1478, 1466, 1873, 1460, 
    1449, 3743, 1454, 1444, 1422
  )

Query time 0.00124

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "336.01"
    },
    "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": 96,
          "rows_produced_per_join": 96,
          "filtered": "100.00",
          "index_condition": "(`test2_gaseus`.`p`.`product_id` in (6201,6202,6203,6204,6205,6206,6207,6208,3033,3034,3035,3036,3857,3858,3859,3860,2715,2716,2717,2718,2719,6303,6304,6305,6306,1500,1501,1502,1503,3726,5206,5207,5208,5209,6736,1490,1491,1492,1493,3724,5198,5199,5200,5201,6735,1495,1496,1497,1498,3164,3725,5202,5203,5204,5205,1485,1486,1487,1488,3163,3723,5194,5195,5196,5197,1429,1430,1431,1432,3161,3719,3720,5142,5143,5144,6730,6731,3158,3159,3160,2102,2039,1985,1472,1484,5613,1428,1478,1466,1873,1460,1449,3743,1454,1444,1422))",
          "cost_info": {
            "read_cost": "57.61",
            "eval_cost": "9.60",
            "prefix_cost": "67.21",
            "data_read_per_join": "1K"
          },
          "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": [
            "test2_gaseus.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 96,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "24.00",
            "eval_cost": "9.60",
            "prefix_cost": "100.81",
            "data_read_per_join": "37K"
          },
          "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": [
            "test2_gaseus.p.group_id"
          ],
          "rows_examined_per_scan": 7,
          "rows_produced_per_join": 672,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "168.00",
            "eval_cost": "67.20",
            "prefix_cost": "336.01",
            "data_read_per_join": "68K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}