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 (
    41, 332, 330, 333, 329, 327, 504, 1008, 
    758, 1817, 1819, 1172, 1196, 1908, 1809, 
    1405, 1851, 730, 1481, 382, 453, 425, 
    360, 1479, 1478, 414, 1866, 788, 1084, 
    1903, 538, 759
  ) 
  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.00171

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": 32,
      "filtered": 6.530610561,
      "index_condition": "cscart_product_prices.product_id in (41,332,330,333,329,327,504,1008,758,1817,1819,1172,1196,1908,1809,1405,1851,730,1481,382,453,425,360,1479,1478,414,1866,788,1084,1903,538,759)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
41 27000.00000000
327 34000.00000000
329 40000.00000000
330 40000.00000000
332 40000.00000000
333 42000.00000000
360 50000.00000000
382 25500.00000000
414 38000.00000000
425 38000.00000000
453 50000.00000000
504 40000.00000000
538 54000.00000000
730 40000.00000000
758 46000.00000000
759 40000.00000000
788 38000.00000000
1008 42000.00000000
1084 50000.00000000
1172 23500.00000000
1196 23500.00000000
1405 27000.00000000
1478 44000.00000000
1479 42000.00000000
1481 24000.00000000
1809 46000.00000000
1817 42000.00000000
1819 42000.00000000
1851 17000.00000000
1866 18500.00000000
1903 42000.00000000
1908 23500.00000000