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 (
    83046, 83047, 90882, 90883, 90961, 90962, 
    85330, 85427, 85478, 85529, 86497, 
    86498, 86773, 86774, 85311, 85312, 
    85408, 85409, 85459, 85460, 85510, 
    85511, 85075, 85161, 85207, 85253, 
    82846, 82919, 82992, 83068, 83476, 
    83484, 83621, 90904, 90983, 83034, 
    83591, 90870, 90949, 85357, 85358, 
    85115, 85376, 82816, 82817, 82889, 
    82890, 82962, 82963, 83336, 83337, 
    83346, 83347, 86488, 86764, 83548, 
    85078, 85164, 85210, 85256, 86623, 
    86692, 82597, 82598, 83037, 83045, 
    83360, 83361, 83594, 90873, 90881, 
    90952, 90960, 82621, 83424, 85118, 
    82627, 83492, 83258, 83265, 86496, 
    86657, 86726, 86772, 85310, 85407, 
    85458, 85509, 86635, 86636, 86704, 
    86705, 85356, 82815, 82888, 82961
  )

Query time 0.00075

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 (83046,83047,90882,90883,90961,90962,85330,85427,85478,85529,86497,86498,86773,86774,85311,85312,85408,85409,85459,85460,85510,85511,85075,85161,85207,85253,82846,82919,82992,83068,83476,83484,83621,90904,90983,83034,83591,90870,90949,85357,85358,85115,85376,82816,82817,82889,82890,82962,82963,83336,83337,83346,83347,86488,86764,83548,85078,85164,85210,85256,86623,86692,82597,82598,83037,83045,83360,83361,83594,90873,90881,90952,90960,82621,83424,85118,82627,83492,83258,83265,86496,86657,86726,86772,85310,85407,85458,85509,86635,86636,86704,86705,85356,82815,82888,82961))",
          "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"
          ]
        }
      }
    ]
  }
}