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 (
    10706, 10707, 10708, 10709, 10710, 10711, 
    10712, 10713, 10714, 10715, 10716, 
    10717, 10718, 10719, 10720, 10721, 
    10722, 10723, 10724, 10725, 10726, 
    10727, 10728, 10729, 10730, 10731, 
    10732, 10733, 10734, 10735, 10736, 
    10737, 10738, 10739, 10740, 10741, 
    10742, 10743, 10744, 10745, 10746, 
    10747, 10748, 10749, 10750, 10751, 
    10752, 10753, 10754, 10755, 10756, 
    10757, 10758, 10759, 10880, 10881, 
    10882, 10883, 10884, 10885, 10886, 
    10887, 10888, 10889, 10890, 10891, 
    10892, 10893, 10894, 10895, 10896, 
    10897, 10898, 11320
  )

Query time 0.00077

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "155.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": 74,
          "rows_produced_per_join": 74,
          "filtered": "100.00",
          "index_condition": "(`gaseus`.`p`.`product_id` in (10706,10707,10708,10709,10710,10711,10712,10713,10714,10715,10716,10717,10718,10719,10720,10721,10722,10723,10724,10725,10726,10727,10728,10729,10730,10731,10732,10733,10734,10735,10736,10737,10738,10739,10740,10741,10742,10743,10744,10745,10746,10747,10748,10749,10750,10751,10752,10753,10754,10755,10756,10757,10758,10759,10880,10881,10882,10883,10884,10885,10886,10887,10888,10889,10890,10891,10892,10893,10894,10895,10896,10897,10898,11320))",
          "cost_info": {
            "read_cost": "44.41",
            "eval_cost": "7.40",
            "prefix_cost": "51.81",
            "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": [
            "gaseus.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 74,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "18.50",
            "eval_cost": "7.40",
            "prefix_cost": "77.71",
            "data_read_per_join": "28K"
          },
          "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": [
            "gaseus.p.group_id"
          ],
          "rows_examined_per_scan": 3,
          "rows_produced_per_join": 222,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "55.50",
            "eval_cost": "22.20",
            "prefix_cost": "155.41",
            "data_read_per_join": "22K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}