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 (
    1401, 1402, 1403, 1384, 727, 729, 726, 
    1583, 251, 237, 238, 373, 372, 339, 365, 
    236, 786, 381, 389, 1914, 1321, 1368, 
    1877, 1876, 1878, 1806, 1794, 1807, 
    1884, 1886, 1889, 1890, 1881, 1879, 
    1880, 1882, 1888, 1871, 1887, 715, 714, 
    1485, 1915, 345, 1451, 1183, 18, 17
  ) 
  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.00185

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 (1401,1402,1403,1384,727,729,726,1583,251,237,238,373,372,339,365,236,786,381,389,1914,1321,1368,1877,1876,1878,1806,1794,1807,1884,1886,1889,1890,1881,1879,1880,1882,1888,1871,1887,715,714,1485,1915,345,1451,1183,18,17)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
17 22500.00000000
18 22500.00000000
236 35000.00000000
237 35000.00000000
238 34000.00000000
251 11000.00000000
339 0.00000000
345 34000.00000000
365 33000.00000000
372 35000.00000000
373 35000.00000000
381 33000.00000000
389 34000.00000000
714 22000.00000000
715 27000.00000000
726 28000.00000000
727 43000.00000000
729 42000.00000000
786 0.00000000
1183 38000.00000000
1321 20000.00000000
1368 20000.00000000
1384 55000.00000000
1401 55000.00000000
1402 55000.00000000
1403 55000.00000000
1451 37000.00000000
1485 22000.00000000
1583 12000.00000000
1794 5000.00000000
1806 5000.00000000
1807 5000.00000000
1871 48000.00000000
1876 44000.00000000
1877 44000.00000000
1878 44000.00000000
1879 44000.00000000
1880 44000.00000000
1881 44000.00000000
1882 48000.00000000
1884 48000.00000000
1886 48000.00000000
1887 48000.00000000
1888 48000.00000000
1889 48000.00000000
1890 48000.00000000
1914 55000.00000000
1915 72000.00000000