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 (
    12505, 13099, 12514, 12513, 9759, 10496, 
    10560, 10561, 10581, 10102, 9940, 10578, 
    10143, 9947, 10567, 10568, 10565, 9933, 
    12499, 12500, 10200, 10201, 10566, 
    10545, 10227, 10552, 12506, 10693, 
    12508, 9953, 12522, 12507
  ) 
  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.00076

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": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (12505,13099,12514,12513,9759,10496,10560,10561,10581,10102,9940,10578,10143,9947,10567,10568,10565,9933,12499,12500,10200,10201,10566,10545,10227,10552,12506,10693,12508,9953,12522,12507)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9759 1955.00000000
9933 1563.00000000
9940 2142.00000000
9947 2022.00000000
9953 1960.00000000
10102 2142.00000000
10143 2205.00000000
10200 2234.00000000
10201 2234.00000000
10227 1997.00000000
10496 1955.00000000
10545 2143.00000000
10552 2143.00000000
10560 2098.00000000
10561 2098.00000000
10565 2691.00000000
10566 2089.00000000
10567 2691.00000000
10568 2691.00000000
10578 2205.00000000
10581 2142.00000000
10693 2089.00000000
12499 0.00000000
12500 0.00000000
12505 0.00000000
12506 0.00000000
12507 0.00000000
12508 0.00000000
12513 0.00000000
12514 0.00000000
12522 0.00000000
13099 0.00000000