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 (
    94638, 94639, 94640, 94671, 94672, 94673, 
    94674, 94822, 94839, 94840, 94841, 
    94844, 94845, 94846, 94847, 94848, 
    94952, 94953, 94954, 94955, 95310, 
    95311, 95317, 95319, 95320, 95361, 
    95362, 95363, 95364, 95365, 95366, 
    95367, 95368, 95369, 95370, 95371, 
    95372, 95373, 95374, 95375, 95377, 
    95392, 95393, 95442, 95592, 95593, 
    95594, 95595, 95596, 95597, 95598, 
    95599, 95600, 95601, 95602, 95603, 
    95604, 95605, 95606, 95607, 95608, 
    95609, 95610, 95611, 95612, 95613, 
    95614, 95615, 95616, 95617, 95618, 
    95619, 95620, 95621, 95622, 95623, 
    95624, 95625, 95626, 95627, 95628, 
    95764, 95766, 95767, 95780, 95781, 
    95782, 95783, 95784, 95785, 95829, 
    95890, 95893, 95925, 95926, 95927
  )

Query time 0.00078

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 (94638,94639,94640,94671,94672,94673,94674,94822,94839,94840,94841,94844,94845,94846,94847,94848,94952,94953,94954,94955,95310,95311,95317,95319,95320,95361,95362,95363,95364,95365,95366,95367,95368,95369,95370,95371,95372,95373,95374,95375,95377,95392,95393,95442,95592,95593,95594,95595,95596,95597,95598,95599,95600,95601,95602,95603,95604,95605,95606,95607,95608,95609,95610,95611,95612,95613,95614,95615,95616,95617,95618,95619,95620,95621,95622,95623,95624,95625,95626,95627,95628,95764,95766,95767,95780,95781,95782,95783,95784,95785,95829,95890,95893,95925,95926,95927))",
          "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"
          ]
        }
      }
    ]
  }
}