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 (
    389305, 389395, 435251, 435249, 435246, 
    389250, 435244, 389297, 435247, 389386, 
    389350, 435253, 389272, 389383, 389406, 
    389423, 389424, 435252, 389301, 389345, 
    435250, 389429, 389245, 389402, 389436, 
    389234, 435242, 389405, 390096, 389422, 
    389369, 389333, 389389, 389391, 389445, 
    389403, 389398, 389302, 390110, 389292, 
    389340, 389379, 389414, 389355, 389328, 
    389253, 389362, 389270, 389435, 389368, 
    389251, 389249, 389308, 389254, 389358, 
    427186, 389400, 422407, 389274, 389367, 
    389342, 389434, 422414, 422413, 391709, 
    389243, 389390, 389385, 422412, 389247, 
    389352, 435215, 389361, 389334, 435214, 
    415533, 389363, 425348, 389351, 389420, 
    389307, 389327, 391713, 389324, 415706, 
    389354, 389330, 389311, 389348, 415696, 
    389281, 415534, 389372, 415532, 389442, 
    415535, 389246, 389329, 389364, 389450
  )

Query time 0.00159

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "430.18"
    },
    "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": 100,
          "rows_produced_per_join": 100,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "120.12",
            "eval_cost": "20.00",
            "prefix_cost": "140.12",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ],
          "attached_condition": "(`webmarco`.`p`.`product_id` in (389305,389395,435251,435249,435246,389250,435244,389297,435247,389386,389350,435253,389272,389383,389406,389423,389424,435252,389301,389345,435250,389429,389245,389402,389436,389234,435242,389405,390096,389422,389369,389333,389389,389391,389445,389403,389398,389302,390110,389292,389340,389379,389414,389355,389328,389253,389362,389270,389435,389368,389251,389249,389308,389254,389358,427186,389400,422407,389274,389367,389342,389434,422414,422413,391709,389243,389390,389385,422412,389247,389352,435215,389361,389334,435214,415533,389363,425348,389351,389420,389307,389327,391713,389324,415706,389354,389330,389311,389348,415696,389281,415534,389372,415532,389442,415535,389246,389329,389364,389450))"
        }
      },
      {
        "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": 100,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "100.00",
            "eval_cost": "20.00",
            "prefix_cost": "260.12",
            "data_read_per_join": "39K"
          },
          "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": 141,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "141.72",
            "eval_cost": "28.34",
            "prefix_cost": "430.18",
            "data_read_per_join": "14K"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      }
    ]
  }
}