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 (
    111835, 111837, 112217, 404082, 406110, 
    429201, 431252, 456772, 450232, 419858, 
    413105, 437380, 442173, 412957, 452843, 
    424041, 421546, 425061, 424958, 415058, 
    412399, 439272, 413649, 457164, 428005, 
    450236, 412114, 408752, 444980, 458748, 
    427822, 428274, 411586, 405254, 416803, 
    419871, 415819, 435233, 446751, 460445, 
    456571, 450261, 446789, 414886, 444321, 
    406390, 425085, 401286, 447216, 440066, 
    456947, 416102, 419585, 439809, 429487, 
    429075, 437962, 413207, 429866, 404231, 
    414955, 430665, 451077, 403015, 438963, 
    401687, 443424, 457801, 454192, 414432, 
    436128, 421748, 421122, 417392, 412740, 
    446814, 414479, 408867, 432076, 443506, 
    447548, 410643, 457524, 312424, 312425, 
    414573, 451444, 413123, 422426, 446892, 
    450729, 404877, 460518, 418662, 403670, 
    413860
  )

Query time 0.00043

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.03140489,
    "nested_loop": [
      {
        "table": {
          "table_name": "g",
          "access_type": "index",
          "possible_keys": ["PRIMARY"],
          "key": "idx_group_code",
          "key_length": "387",
          "used_key_parts": ["code"],
          "loops": 1,
          "rows": 2,
          "cost": 0.00693929,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "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": ["u985510652_ecartify.g.id"],
          "loops": 2,
          "rows": 1,
          "cost": 0.00527872,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "p",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "idx_group_id"],
          "key": "idx_group_id",
          "key_length": "3",
          "used_key_parts": ["group_id"],
          "ref": ["u985510652_ecartify.g.id"],
          "loops": 2,
          "rows": 8,
          "cost": 0.01918688,
          "filtered": 100,
          "index_condition": "p.product_id in (111835,111837,112217,404082,406110,429201,431252,456772,450232,419858,413105,437380,442173,412957,452843,424041,421546,425061,424958,415058,412399,439272,413649,457164,428005,450236,412114,408752,444980,458748,427822,428274,411586,405254,416803,419871,415819,435233,446751,460445,456571,450261,446789,414886,444321,406390,425085,401286,447216,440066,456947,416102,419585,439809,429487,429075,437962,413207,429866,404231,414955,430665,451077,403015,438963,401687,443424,457801,454192,414432,436128,421748,421122,417392,412740,446814,414479,408867,432076,443506,447548,410643,457524,312424,312425,414573,451444,413123,422426,446892,450729,404877,460518,418662,403670,413860)"
        }
      }
    ]
  }
}