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 (
    93465, 93466, 93467, 93468, 93469, 93470, 
    93471, 93472, 93473, 93474, 93475, 
    93476, 93477, 93478, 93479, 93480, 
    93481, 93482, 93483, 93484, 93485, 
    93486, 93487, 93488, 93489, 93490, 
    93491, 93492, 93493, 93494, 93495, 
    93496, 93497, 93498, 93499, 93500, 
    93501, 93502, 93503, 93504, 93505, 
    93506, 93507, 93508, 93509, 93510, 
    93511, 93512, 93513, 93514, 93529, 
    93530, 93531, 93532, 93533, 93534, 
    93535, 93536, 93537, 93538, 93539, 
    93540, 93541, 93542, 93543, 93544, 
    93545, 93546, 93547, 93548, 93549, 
    93550, 93551, 93552, 93553, 93554, 
    93555, 93556, 93557, 93558, 93559, 
    93560, 93561, 93562, 93563, 93564, 
    93565, 93566, 93567, 93568, 93569, 
    93570, 93571, 93572, 93573, 93574
  )

Query time 0.00071

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.80"
    },
    "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": "1.00",
            "eval_cost": "0.10",
            "prefix_cost": "1.45",
            "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 (93465,93466,93467,93468,93469,93470,93471,93472,93473,93474,93475,93476,93477,93478,93479,93480,93481,93482,93483,93484,93485,93486,93487,93488,93489,93490,93491,93492,93493,93494,93495,93496,93497,93498,93499,93500,93501,93502,93503,93504,93505,93506,93507,93508,93509,93510,93511,93512,93513,93514,93529,93530,93531,93532,93533,93534,93535,93536,93537,93538,93539,93540,93541,93542,93543,93544,93545,93546,93547,93548,93549,93550,93551,93552,93553,93554,93555,93556,93557,93558,93559,93560,93561,93562,93563,93564,93565,93566,93567,93568,93569,93570,93571,93572,93573,93574))",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "1.80",
            "data_read_per_join": "16"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ]
        }
      }
    ]
  }
}