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 (
    1230, 1229, 1228, 1227, 1226, 1225, 1224, 
    1223, 1222, 1221, 1220, 1219, 1218, 
    1217, 1216, 1215, 1170, 1171, 1177, 
    1175, 1168, 1183, 1184, 1174, 1187, 
    67, 1191, 1176, 1167, 1169, 1194, 1179, 
    1180, 1181, 1197, 1182, 1201, 1202, 
    1198, 1178, 603, 1185, 1209, 1186, 1210, 
    1211, 1203, 1212, 1213, 1188, 1190, 
    1193, 1207, 1208, 1195, 1196, 1199, 
    1205, 1173, 1200, 1172, 15, 16, 17, 18, 
    19, 20, 21, 22, 26, 27, 28, 29, 30, 31, 
    32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 
    42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 
    52
  ) 
  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.00296

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": 96,
      "filtered": 19.59183121,
      "index_condition": "cscart_product_prices.product_id in (1230,1229,1228,1227,1226,1225,1224,1223,1222,1221,1220,1219,1218,1217,1216,1215,1170,1171,1177,1175,1168,1183,1184,1174,1187,67,1191,1176,1167,1169,1194,1179,1180,1181,1197,1182,1201,1202,1198,1178,603,1185,1209,1186,1210,1211,1203,1212,1213,1188,1190,1193,1207,1208,1195,1196,1199,1205,1173,1200,1172,15,16,17,18,19,20,21,22,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
15 0.00000000
16 85909.00000000
17 22500.00000000
18 22500.00000000
19 22500.00000000
20 44400.00000000
21 58000.00000000
22 36000.00000000
26 0.00000000
27 52000.00000000
28 52000.00000000
29 44000.00000000
30 37000.00000000
31 37000.00000000
32 44400.00000000
33 20000.00000000
34 44000.00000000
35 282000.00000000
36 110000.00000000
37 96000.00000000
38 21000.00000000
39 80000.00000000
40 58000.00000000
41 27000.00000000
42 120000.00000000
43 120000.00000000
44 288000.00000000
45 122000.00000000
46 318000.00000000
47 156000.00000000
48 165000.00000000
49 69600.00000000
50 0.00000000
51 510000.00000000
52 0.00000000
67 48000.00000000
603 0.00000000
1167 48000.00000000
1168 32000.00000000
1169 170000.00000000
1170 29000.00000000
1171 88000.00000000
1172 23500.00000000
1173 30000.00000000
1174 91000.00000000
1175 70200.00000000
1176 83000.00000000
1177 48000.00000000
1178 60000.00000000
1179 260000.00000000
1180 24000.00000000
1181 34000.00000000
1182 65000.00000000
1183 38000.00000000
1184 70000.00000000
1185 26000.00000000
1186 21000.00000000
1187 47000.00000000
1188 14500.00000000
1190 21000.00000000
1191 83000.00000000
1193 150000.00000000
1194 42000.00000000
1195 60000.00000000
1196 23500.00000000
1197 58000.00000000
1198 30000.00000000
1199 40000.00000000
1200 40000.00000000
1201 65000.00000000
1202 46000.00000000
1203 12500.00000000
1205 22000.00000000
1207 12500.00000000
1208 58000.00000000
1209 21000.00000000
1210 12500.00000000
1211 40000.00000000
1212 40000.00000000
1213 21000.00000000
1215 0.00000000
1216 88000.00000000
1217 122000.00000000
1218 335000.00000000
1219 56000.00000000
1220 56000.00000000
1221 195000.00000000
1222 54000.00000000
1223 23000.00000000
1224 54000.00000000
1225 82300.00000000
1226 82300.00000000
1227 125000.00000000
1228 63000.00000000
1229 50000.00000000
1230 45000.00000000