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 (
    1306, 1307, 1310, 1308, 1309, 1311, 1314, 
    1313, 1318, 1319, 1315, 1316, 1317, 
    56, 1299, 1298, 1297, 1260, 1296, 1295, 
    990, 1294, 1293, 1129, 1278, 1269, 1261, 
    1292, 1291, 1290, 1289, 1288, 1287, 
    1286, 1285, 1284, 1283, 1282, 1275, 
    1281, 1280, 1279, 1274, 1145, 1134, 
    1273, 1272, 93
  ) 
  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.00177

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": 48,
      "filtered": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (1306,1307,1310,1308,1309,1311,1314,1313,1318,1319,1315,1316,1317,56,1299,1298,1297,1260,1296,1295,990,1294,1293,1129,1278,1269,1261,1292,1291,1290,1289,1288,1287,1286,1285,1284,1283,1282,1275,1281,1280,1279,1274,1145,1134,1273,1272,93)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
56 32000.00000000
93 35000.00000000
990 235000.00000000
1129 72000.00000000
1134 70000.00000000
1145 29000.00000000
1260 230000.00000000
1261 72000.00000000
1269 38000.00000000
1272 48000.00000000
1273 190000.00000000
1274 60000.00000000
1275 0.00000000
1278 72000.00000000
1279 32000.00000000
1280 0.00000000
1281 32000.00000000
1282 64000.00000000
1283 20000.00000000
1284 20000.00000000
1285 22000.00000000
1286 22000.00000000
1287 22000.00000000
1288 21000.00000000
1289 21000.00000000
1290 56000.00000000
1291 56000.00000000
1292 56000.00000000
1293 165000.00000000
1294 390000.00000000
1295 435000.00000000
1296 650000.00000000
1297 59000.00000000
1298 59000.00000000
1299 48000.00000000
1306 0.00000000
1307 0.00000000
1308 70000.00000000
1309 0.00000000
1310 204000.00000000
1311 18000.00000000
1313 225000.00000000
1314 32000.00000000
1315 210000.00000000
1316 22000.00000000
1317 42000.00000000
1318 35000.00000000
1319 180000.00000000