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 (
    92203, 92449, 92168, 86857, 85037, 91870, 
    91869, 91871, 91872, 91873, 91874, 
    91875, 85038, 92458, 92459, 92460, 
    92461, 92462, 92463, 92464, 86892, 
    92211, 92216, 92218, 92219, 92220, 
    92221, 92222, 92224, 92225, 92226, 
    92467, 86894, 92232, 92233, 92234, 
    92235, 92236, 92238, 92468, 92472, 
    92473, 92474, 92212, 92213, 92214, 
    92215, 92217, 92223, 92465, 92466, 
    92191, 92228, 92229, 92230, 92231, 
    92237, 92469, 92470, 92471, 91865, 
    85585, 92366, 92367, 92368, 92369, 
    92370, 92371, 92372, 91892, 92150, 
    92197, 92457, 85646, 91866, 91867, 
    92325, 92326, 92327, 92328, 85578, 
    92440, 92186, 92187, 92188, 92189, 
    92190, 92441, 85642, 85582, 86901, 
    91840, 91841, 91842, 91843, 91845
  )

Query time 0.00066

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 (92203,92449,92168,86857,85037,91870,91869,91871,91872,91873,91874,91875,85038,92458,92459,92460,92461,92462,92463,92464,86892,92211,92216,92218,92219,92220,92221,92222,92224,92225,92226,92467,86894,92232,92233,92234,92235,92236,92238,92468,92472,92473,92474,92212,92213,92214,92215,92217,92223,92465,92466,92191,92228,92229,92230,92231,92237,92469,92470,92471,91865,85585,92366,92367,92368,92369,92370,92371,92372,91892,92150,92197,92457,85646,91866,91867,92325,92326,92327,92328,85578,92440,92186,92187,92188,92189,92190,92441,85642,85582,86901,91840,91841,91842,91843,91845))",
          "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"
          ]
        }
      }
    ]
  }
}