SELECT 
  cscart_product_prices.product_id, 
  MIN(
    IF(
      cscart_product_prices.percentage_discount = 0, 
      cscart_product_prices.price, 
      cscart_product_prices.price - (
        cscart_product_prices.price * cscart_product_prices.percentage_discount
      )/ 100
    )
  ) AS price 
FROM 
  cscart_product_prices 
WHERE 
  cscart_product_prices.product_id IN (
    12259, 12260, 12261, 12262, 12263, 12264, 
    12265, 12266, 12267, 12268, 12269, 
    12270, 12271, 12272, 12273, 12274, 
    12275, 12276, 12277, 12278, 12279, 
    12280, 12281, 12282, 12283, 12284, 
    12285, 12286, 12287, 12288, 12289, 
    12290, 12291, 12292, 12293, 12294, 
    12295, 12296, 12297, 12298, 12299, 
    12300, 12301, 12302, 12303, 12304, 
    12305, 12306, 12307, 12308, 12309, 
    12310, 12311, 12312, 12313, 12314, 
    12315, 12316, 12317, 12318, 12319, 
    12320, 12321, 12322, 12323, 12324, 
    12325, 12326, 12327, 12328, 12329, 
    12330, 12331, 12332, 12333, 12334, 
    12335, 12336, 12337, 12338, 12339, 
    12340, 12341, 12342, 12343, 12344, 
    12345, 12346, 12347, 12348
  ) 
  AND cscart_product_prices.lower_limit = 1 
  AND cscart_product_prices.usergroup_id IN (0, 1) 
GROUP BY 
  cscart_product_prices.product_id

Query time 0.00157

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "63.01"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "product_id",
        "used_key_parts": [
          "product_id"
        ],
        "key_length": "3",
        "rows_examined_per_scan": 90,
        "rows_produced_per_join": 17,
        "filtered": "19.99",
        "index_condition": "(`test2_gaseus`.`cscart_product_prices`.`product_id` in (12259,12260,12261,12262,12263,12264,12265,12266,12267,12268,12269,12270,12271,12272,12273,12274,12275,12276,12277,12278,12279,12280,12281,12282,12283,12284,12285,12286,12287,12288,12289,12290,12291,12292,12293,12294,12295,12296,12297,12298,12299,12300,12301,12302,12303,12304,12305,12306,12307,12308,12309,12310,12311,12312,12313,12314,12315,12316,12317,12318,12319,12320,12321,12322,12323,12324,12325,12326,12327,12328,12329,12330,12331,12332,12333,12334,12335,12336,12337,12338,12339,12340,12341,12342,12343,12344,12345,12346,12347,12348))",
        "cost_info": {
          "read_cost": "61.21",
          "eval_cost": "1.80",
          "prefix_cost": "63.01",
          "data_read_per_join": "431"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`test2_gaseus`.`cscart_product_prices`.`lower_limit` = 1) and (`test2_gaseus`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}