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 (
    388490, 389030, 389871, 389634, 391751, 
    389633, 434582, 389085, 388407, 388468, 
    388426, 390173, 389214, 389775, 388460, 
    415508, 388600, 388752, 435302, 388436, 
    389021, 389131, 389036, 435306, 389150, 
    389144, 434578, 390114, 389109, 389149, 
    390194, 426898, 388634, 389124, 435293, 
    389774, 389113, 388774, 415317, 388755, 
    389215, 388495, 390124, 388598, 388442, 
    388580, 389062, 435301, 388777, 389803, 
    435296, 388628, 388432, 388737, 389821, 
    391750, 435294, 388492, 415313, 388773, 
    389107, 388792, 415476, 389809, 435300, 
    435305, 388545, 435304, 388851, 388514, 
    388601, 389192, 388433, 388632, 388381, 
    390201, 427200, 389467, 415582, 390113, 
    389200, 435303, 388510, 388788, 425347, 
    390241, 388646, 388796, 389091, 388698, 
    388735, 415385, 389198, 388429, 388465, 
    388746, 390112, 389482, 390168, 391773
  )

Query time 0.00113

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 (388490,389030,389871,389634,391751,389633,434582,389085,388407,388468,388426,390173,389214,389775,388460,415508,388600,388752,435302,388436,389021,389131,389036,435306,389150,389144,434578,390114,389109,389149,390194,426898,388634,389124,435293,389774,389113,388774,415317,388755,389215,388495,390124,388598,388442,388580,389062,435301,388777,389803,435296,388628,388432,388737,389821,391750,435294,388492,415313,388773,389107,388792,415476,389809,435300,435305,388545,435304,388851,388514,388601,389192,388433,388632,388381,390201,427200,389467,415582,390113,389200,435303,388510,388788,425347,390241,388646,388796,389091,388698,388735,415385,389198,388429,388465,388746,390112,389482,390168,391773))"
        }
      },
      {
        "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"
          ]
        }
      }
    ]
  }
}