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 (
    97688, 97689, 97690, 97691, 97692, 97693, 
    97694, 97695, 97696, 97707, 97708, 
    97709, 97710, 97711, 97712, 97728, 
    97729, 97730, 97731, 97732, 97733, 
    97734, 97735, 97736, 97737, 97738, 
    97739, 97740, 97741, 97742, 97743, 
    97744, 97745, 97746, 97747, 97748, 
    97749, 97750, 97751, 97752, 97753, 
    97754, 97755, 97756, 97757, 97758, 
    97759, 97760, 97761, 97762, 97763, 
    97764, 97778, 97779, 97780, 97781, 
    97782, 97790, 97791, 97792, 97793, 
    97794, 97795, 97796, 97797, 97798, 
    97818, 97819, 97820, 97821, 97822, 
    97823, 97824, 97825, 97826, 97831, 
    97832, 97874, 97875, 97876, 97877, 
    97878, 97879, 97880, 97881, 97882, 
    97883, 97884, 97885, 97886, 97887, 
    97888, 97889, 97959, 98001, 98002
  )

Query time 0.00025

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.05"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "f",
          "access_type": "ALL",
          "possible_keys": [
            "idx_group_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": "104"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "g",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "3",
          "ref": [
            "nuie_scalesta_net.f.group_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.70",
            "data_read_per_join": "400"
          },
          "used_columns": [
            "id",
            "code"
          ]
        }
      },
      {
        "table": {
          "table_name": "p",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "nuie_scalesta_net.f.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "index_condition": "(`nuie_scalesta_net`.`p`.`product_id` in (97688,97689,97690,97691,97692,97693,97694,97695,97696,97707,97708,97709,97710,97711,97712,97728,97729,97730,97731,97732,97733,97734,97735,97736,97737,97738,97739,97740,97741,97742,97743,97744,97745,97746,97747,97748,97749,97750,97751,97752,97753,97754,97755,97756,97757,97758,97759,97760,97761,97762,97763,97764,97778,97779,97780,97781,97782,97790,97791,97792,97793,97794,97795,97796,97797,97798,97818,97819,97820,97821,97822,97823,97824,97825,97826,97831,97832,97874,97875,97876,97877,97878,97879,97880,97881,97882,97883,97884,97885,97886,97887,97888,97889,97959,98001,98002))",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "1.05",
            "data_read_per_join": "16"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ]
        }
      }
    ]
  }
}