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 (
    91084, 91085, 86902, 86927, 86963, 86988, 
    87024, 87049, 87085, 87107, 83569, 
    83571, 90580, 90581, 90851, 90853, 
    90930, 90932, 90585, 90919, 90998, 
    91011, 91012, 91013, 91023, 91024, 
    91025, 91035, 91036, 91037, 91048, 
    91049, 91050, 89491, 89492, 89499, 
    89500, 89507, 89508, 89515, 89516, 
    83254, 83256, 83261, 83263, 90562, 
    90563, 90568, 90569, 90574, 90575, 
    91090, 91091, 91094, 91095, 91097, 
    91098, 91099, 91176, 91177, 91180, 
    91181, 91183, 91184, 91185, 91231, 
    91232, 91235, 91236, 91238, 91239, 
    91240, 83268, 83270, 90544, 90545, 
    91070, 91071, 91073, 91074, 91075, 
    90567, 90573, 90579, 87308, 87309, 
    87320, 87321, 87332, 87333, 87344, 
    87345, 90555, 90582, 90855, 90934
  )

Query time 0.00074

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.05"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "f",
          "access_type": "ALL",
          "possible_keys": [
            "idx_group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "0.35",
            "data_read_per_join": "104"
          },
          "used_columns": [
            "feature_id",
            "purpose",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "g",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "3",
          "ref": [
            "nuie_scalesta_net.f.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "0.70",
            "data_read_per_join": "400"
          },
          "used_columns": [
            "id",
            "code"
          ]
        }
      },
      {
        "table": {
          "table_name": "p",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY",
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "nuie_scalesta_net.f.group_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "index_condition": "(`nuie_scalesta_net`.`p`.`product_id` in (91084,91085,86902,86927,86963,86988,87024,87049,87085,87107,83569,83571,90580,90581,90851,90853,90930,90932,90585,90919,90998,91011,91012,91013,91023,91024,91025,91035,91036,91037,91048,91049,91050,89491,89492,89499,89500,89507,89508,89515,89516,83254,83256,83261,83263,90562,90563,90568,90569,90574,90575,91090,91091,91094,91095,91097,91098,91099,91176,91177,91180,91181,91183,91184,91185,91231,91232,91235,91236,91238,91239,91240,83268,83270,90544,90545,91070,91071,91073,91074,91075,90567,90573,90579,87308,87309,87320,87321,87332,87333,87344,87345,90555,90582,90855,90934))",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "1.05",
            "data_read_per_join": "16"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ]
        }
      }
    ]
  }
}