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 (
    98453, 98455, 98456, 98476, 98477, 98478, 
    98479, 98480, 98481, 98482, 98483, 
    98484, 98485, 98486, 98487, 98488, 
    98489, 98490, 98491, 98492, 98493, 
    98494, 98495, 98503, 98504, 98505, 
    98506, 98507, 98508, 98523, 98524, 
    98525, 98526, 98527, 98528, 98529, 
    98530, 98531, 98532, 98533, 98534, 
    98535, 98536, 98537, 98538, 98539, 
    98540, 98541, 98542, 98543, 98544, 
    98545, 98546, 98547, 98548, 98549, 
    98557, 98558, 98559, 98563, 98564, 
    98565, 98566, 98567, 98568, 98569, 
    98690, 98693, 98694, 98758, 98759, 
    98762, 98769, 98770, 98771, 98772, 
    98773, 98774, 98775, 98776, 98777, 
    98778, 98779, 98780, 98781, 98782, 
    98783, 98784, 98785, 98786, 98787, 
    98788, 98789, 98790, 98791, 98792
  )

Query time 0.00052

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 (98453,98455,98456,98476,98477,98478,98479,98480,98481,98482,98483,98484,98485,98486,98487,98488,98489,98490,98491,98492,98493,98494,98495,98503,98504,98505,98506,98507,98508,98523,98524,98525,98526,98527,98528,98529,98530,98531,98532,98533,98534,98535,98536,98537,98538,98539,98540,98541,98542,98543,98544,98545,98546,98547,98548,98549,98557,98558,98559,98563,98564,98565,98566,98567,98568,98569,98690,98693,98694,98758,98759,98762,98769,98770,98771,98772,98773,98774,98775,98776,98777,98778,98779,98780,98781,98782,98783,98784,98785,98786,98787,98788,98789,98790,98791,98792))",
          "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"
          ]
        }
      }
    ]
  }
}