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 (
    40756, 822, 27949, 38103, 3409, 38158, 
    19210, 35735, 37665, 448, 27927, 2588, 
    1128, 5816, 46586, 825, 37400, 35733, 
    6643, 3695, 1419, 32319, 37320, 27366, 
    32775, 815, 31732, 32306, 32304, 3013, 
    1445, 33286
  )

Query time 0.00909

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "p",
          "access_type": "range",
          "possible_keys": ["PRIMARY", "idx_group_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "rows": 33,
          "filtered": 100,
          "index_condition": "p.product_id in (40756,822,27949,38103,3409,38158,19210,35735,37665,448,27927,2588,1128,5816,46586,825,37400,35733,6643,3695,1419,32319,37320,27366,32775,815,31732,32306,32304,3013,1445,33286)"
        }
      },
      {
        "table": {
          "table_name": "f",
          "access_type": "ref",
          "possible_keys": ["idx_group_id"],
          "key": "idx_group_id",
          "key_length": "3",
          "used_key_parts": ["group_id"],
          "ref": ["dev_db.p.group_id"],
          "rows": 1,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "g",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["id"],
          "ref": ["dev_db.p.group_id"],
          "rows": 1,
          "filtered": 100
        }
      }
    ]
  }
}