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 (
    92556, 86235, 86238, 86229, 89593, 89594, 
    86231, 86228, 86223, 89004, 91961, 
    91962, 91963, 91964, 91965, 91966, 
    91967, 86227, 89591, 89592, 86226, 
    86237, 86230, 91960, 91953, 91954, 
    91955, 91956, 91957, 91958, 91959, 
    86236, 86222, 86224, 82333, 86225, 
    82335, 82401, 82409, 82419, 86234, 
    82384, 82345, 91952, 82400, 82408, 
    82418, 82344, 86232, 86233, 92555, 
    94705, 94706, 96706, 96707, 96715, 
    96717, 96726, 96727, 96748, 96756, 
    96757, 96762, 96763, 96771, 96772, 
    97829, 97830, 98274, 98275, 98276, 
    98277, 98278, 98279, 98280, 98281, 
    98282, 98283, 98284, 98285, 98286, 
    98287, 98288, 98289, 98572, 98573, 
    98574, 98575, 98576, 98577, 98578, 
    98579, 98580, 98581, 98582, 98583
  )

Query time 0.00068

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 (92556,86235,86238,86229,89593,89594,86231,86228,86223,89004,91961,91962,91963,91964,91965,91966,91967,86227,89591,89592,86226,86237,86230,91960,91953,91954,91955,91956,91957,91958,91959,86236,86222,86224,82333,86225,82335,82401,82409,82419,86234,82384,82345,91952,82400,82408,82418,82344,86232,86233,92555,94705,94706,96706,96707,96715,96717,96726,96727,96748,96756,96757,96762,96763,96771,96772,97829,97830,98274,98275,98276,98277,98278,98279,98280,98281,98282,98283,98284,98285,98286,98287,98288,98289,98572,98573,98574,98575,98576,98577,98578,98579,98580,98581,98582,98583))",
          "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"
          ]
        }
      }
    ]
  }
}