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 (
    264945, 264756, 264208, 263981, 264156, 
    264888, 264304, 264851, 264940, 264973, 
    264893, 264909, 264950, 264219, 264102, 
    264193, 264922, 264964, 264705, 366828, 
    366833, 371861, 366771, 371848, 371878, 
    366815, 264824, 371873, 366690, 263925, 
    264226, 371890, 264955, 371884, 263720, 
    371868, 366760, 366765, 371898, 264931, 
    371895, 366821, 366796, 371936, 371902, 
    377755, 371912, 372021, 377735, 371984, 
    371917, 371905, 366754, 366841, 371945, 
    371843, 371941, 371988, 371909, 371947, 
    366701, 371929, 371933, 371944, 371949, 
    371935, 371940, 381639, 185269
  )

Query time 0.00206

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "296.75"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "p",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "rows_examined_per_scan": 69,
          "rows_produced_per_join": 69,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "82.89",
            "eval_cost": "13.80",
            "prefix_cost": "96.69",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ],
          "attached_condition": "(`webmarco`.`p`.`product_id` in (264945,264756,264208,263981,264156,264888,264304,264851,264940,264973,264893,264909,264950,264219,264102,264193,264922,264964,264705,366828,366833,371861,366771,371848,371878,366815,264824,371873,366690,263925,264226,371890,264955,371884,263720,371868,366760,366765,371898,264931,371895,366821,366796,371936,371902,377755,371912,372021,377735,371984,371917,371905,366754,366841,371945,371843,371941,371988,371909,371947,366701,371929,371933,371944,371949,371935,371940,381639,185269))"
        }
      },
      {
        "table": {
          "table_name": "g",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "3",
          "ref": [
            "webmarco.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 69,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "69.00",
            "eval_cost": "13.80",
            "prefix_cost": "179.49",
            "data_read_per_join": "26K"
          },
          "used_columns": [
            "id",
            "code"
          ]
        }
      },
      {
        "table": {
          "table_name": "f",
          "access_type": "ref",
          "possible_keys": [
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "webmarco.p.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 97,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "97.72",
            "eval_cost": "19.54",
            "prefix_cost": "296.75",
            "data_read_per_join": "9K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}