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 (
    262440, 264695, 264694, 264697, 339741, 
    339622, 339616, 339574, 339740, 339692, 
    339609, 339737, 339573, 339621, 339620, 
    339617, 339587, 339611, 339742, 339710, 
    339683, 339610, 339584, 339706, 339570, 
    339625, 339638, 339771, 339736, 339770, 
    339615, 339618, 339569, 339581, 339705, 
    339619, 339608, 339693, 339779, 339607, 
    339732, 339572, 339775, 339571, 339589, 
    339778, 339738, 339686, 339586, 339739, 
    339780, 339684, 339777, 339588, 339708, 
    339776, 339575
  )

Query time 0.00095

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "245.21"
    },
    "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": 57,
          "rows_produced_per_join": 57,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "68.47",
            "eval_cost": "11.40",
            "prefix_cost": "79.87",
            "data_read_per_join": "912"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ],
          "attached_condition": "(`webmarco`.`p`.`product_id` in (262440,264695,264694,264697,339741,339622,339616,339574,339740,339692,339609,339737,339573,339621,339620,339617,339587,339611,339742,339710,339683,339610,339584,339706,339570,339625,339638,339771,339736,339770,339615,339618,339569,339581,339705,339619,339608,339693,339779,339607,339732,339572,339775,339571,339589,339778,339738,339686,339586,339739,339780,339684,339777,339588,339708,339776,339575))"
        }
      },
      {
        "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": 57,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "57.00",
            "eval_cost": "11.40",
            "prefix_cost": "148.27",
            "data_read_per_join": "22K"
          },
          "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": 80,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "80.78",
            "eval_cost": "16.16",
            "prefix_cost": "245.21",
            "data_read_per_join": "8K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}