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 (
    5097, 5098, 5099, 5100, 5101, 5102, 5103, 
    5104, 5105, 5106, 2760, 2761, 2762, 
    2763, 2764, 6348, 6349, 6350, 6351, 
    2756, 2757, 2758, 2759, 6343, 6344, 
    6345, 6346, 6347, 2818, 2819, 2820, 
    2821, 2822, 6404, 6405, 6406, 6407, 
    2933, 2934, 2935, 2936, 6517, 6518, 
    6519, 6520, 6521, 2510, 2511, 2512, 
    2513, 3837, 3838, 3839, 6109, 6110, 
    2277, 2278, 2279, 2280, 2327, 2328, 
    2329, 2330, 2394, 2395, 2396, 2397, 
    2398, 2399, 2400, 2401, 2402, 2505, 
    2506, 2507, 2508, 5876, 5877, 5878, 
    5879, 5880, 5926, 5927, 5928, 5929, 
    5930, 5994, 5995, 5996, 5997, 5998, 
    5999, 6000, 6001, 6002, 6105
  )

Query time 0.00109

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "201.61"
    },
    "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": "(`gaseus`.`p`.`product_id` in (5097,5098,5099,5100,5101,5102,5103,5104,5105,5106,2760,2761,2762,2763,2764,6348,6349,6350,6351,2756,2757,2758,2759,6343,6344,6345,6346,6347,2818,2819,2820,2821,2822,6404,6405,6406,6407,2933,2934,2935,2936,6517,6518,6519,6520,6521,2510,2511,2512,2513,3837,3838,3839,6109,6110,2277,2278,2279,2280,2327,2328,2329,2330,2394,2395,2396,2397,2398,2399,2400,2401,2402,2505,2506,2507,2508,5876,5877,5878,5879,5880,5926,5927,5928,5929,5930,5994,5995,5996,5997,5998,5999,6000,6001,6002,6105))",
          "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": [
            "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": [
            "gaseus.p.group_id"
          ],
          "rows_examined_per_scan": 3,
          "rows_produced_per_join": 288,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "72.00",
            "eval_cost": "28.80",
            "prefix_cost": "201.61",
            "data_read_per_join": "29K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}