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 (
    10331, 
    10358, 
    10378, 
    10311, 
    10332, 
    10359, 
    10379, 
    10312, 
    10333, 
    10360, 
    10380, 
    10334, 
    10361, 
    10381, 
    10335, 
    10362, 
    10336, 
    10363, 
    10337, 
    10364, 
    10338, 
    10365, 
    10339, 
    10366, 
    10340, 
    10367, 
    10341, 
    10368, 
    10342, 
    12565, 
    10293, 
    10296, 
    10301, 
    10313, 
    10318, 
    10294, 
    10297, 
    10302, 
    10314, 
    10319, 
    12552, 
    12554, 
    10295, 
    10298, 
    10303, 
    10315, 
    10320, 
    10299, 
    10304, 
    10316, 
    10321, 
    10300, 
    10317, 
    10322, 
    12572, 
    12575, 
    12571, 
    12573, 
    10273, 
    10272, 
    10271, 
    10270, 
    10269, 
    10268, 
    10267, 
    10266, 
    10265, 
    10264, 
    10263, 
    12570, 
    12567, 
    12569, 
    12568, 
    10261, 
    10260, 
    10259, 
    10258, 
    10257, 
    10256, 
    10255, 
    10254, 
    10253, 
    10252, 
    10251, 
    10250, 
    10249, 
    10248, 
    10247, 
    10246, 
    10245, 
    10244, 
    10243, 
    10242, 
    10241, 
    10203, 
    10204, 
    10205, 
    10206, 
    10207, 
    10208, 
    10209, 
    10210, 
    10211, 
    10212, 
    10213, 
    10214, 
    10215, 
    10216, 
    10217, 
    10218, 
    10219, 
    10220, 
    10221, 
    10222, 
    10144, 
    10145, 
    10146, 
    10147, 
    10148, 
    10149, 
    10150, 
    10151, 
    10152, 
    12969, 
    10103, 
    10104, 
    10105, 
    10106
  ) 
  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.00110

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_product_prices",
      "access_type": "range",
      "possible_keys": ["usergroup", "product_id", "lower_limit", "usergroup_id"],
      "key": "product_id",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "rows": 128,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (10331,10358,10378,10311,10332,10359,10379,10312,10333,10360,10380,10334,10361,10381,10335,10362,10336,10363,10337,10364,10338,10365,10339,10366,10340,10367,10341,10368,10342,12565,10293,10296,10301,10313,10318,10294,10297,10302,10314,10319,12552,12554,10295,10298,10303,10315,10320,10299,10304,10316,10321,10300,10317,10322,12572,12575,12571,12573,10273,10272,10271,10270,10269,10268,10267,10266,10265,10264,10263,12570,12567,12569,12568,10261,10260,10259,10258,10257,10256,10255,10254,10253,10252,10251,10250,10249,10248,10247,10246,10245,10244,10243,10242,10241,10203,10204,10205,10206,10207,10208,10209,10210,10211,10212,10213,10214,10215,10216,10217,10218,10219,10220,10221,10222,10144,10145,10146,10147,10148,10149,10150,10151,10152,12969,10103,10104,10105,10106)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10103 4702.31000000
10104 4702.31000000
10105 4702.31000000
10106 4702.31000000
10144 1606.00000000
10145 1606.00000000
10146 1606.00000000
10147 1606.00000000
10148 1606.00000000
10149 1606.00000000
10150 1606.00000000
10151 1606.00000000
10152 1606.00000000
10203 1894.30000000
10204 1894.30000000
10205 1894.30000000
10206 1894.30000000
10207 1894.30000000
10208 1894.30000000
10209 1894.30000000
10210 1894.30000000
10211 1894.30000000
10212 1894.30000000
10213 1894.30000000
10214 1894.30000000
10215 1894.30000000
10216 1894.30000000
10217 1894.30000000
10218 1894.30000000
10219 1894.30000000
10220 1894.30000000
10221 1894.30000000
10222 1894.30000000
10241 1136.58000000
10242 1136.58000000
10243 1136.58000000
10244 1136.58000000
10245 1136.58000000
10246 1136.58000000
10247 1136.58000000
10248 1136.58000000
10249 1136.58000000
10250 1136.58000000
10251 1136.58000000
10252 1136.58000000
10253 1136.58000000
10254 1136.58000000
10255 1136.58000000
10256 1136.58000000
10257 1136.58000000
10258 1136.58000000
10259 1136.58000000
10260 1136.58000000
10261 1136.58000000
10263 2150.58000000
10264 2150.58000000
10265 2150.58000000
10266 2150.58000000
10267 2150.58000000
10268 2150.58000000
10269 2150.58000000
10270 2150.58000000
10271 2150.58000000
10272 2150.58000000
10273 2150.58000000
10293 2206.30000000
10294 2206.30000000
10295 2206.30000000
10296 3409.73000000
10297 3409.73000000
10298 3409.73000000
10299 3409.73000000
10300 3409.73000000
10301 4579.74000000
10302 4579.74000000
10303 4579.74000000
10304 4579.74000000
10311 3225.60000000
10312 3225.60000000
10313 2930.59000000
10314 2930.59000000
10315 2930.59000000
10316 2930.59000000
10317 2930.59000000
10318 2930.59000000
10319 2930.59000000
10320 2930.59000000
10321 2930.59000000
10322 2930.59000000
10331 518.70000000
10332 518.70000000
10333 518.70000000
10334 518.70000000
10335 518.70000000
10336 518.70000000
10337 518.70000000
10338 518.70000000
10339 518.70000000
10340 518.70000000
10341 518.70000000
10342 518.70000000
10358 2121.60000000
10359 2121.60000000
10360 2121.60000000
10361 2121.60000000
10362 2121.60000000
10363 2121.60000000
10364 2121.60000000
10365 2121.60000000
10366 2121.60000000
10367 2121.60000000
10368 2121.60000000
10378 2763.44000000
10379 2763.44000000
10380 2763.44000000
10381 2763.44000000
12552 14984.00000000
12554 19331.00000000
12565 67629.15000000
12567 10552.12000000
12568 10552.12000000
12569 10552.12000000
12570 11031.68000000
12571 67629.15000000
12572 42208.28000000
12573 68108.92000000
12575 47964.00000000
12969 1606.00000000