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 (
    13101, 12498, 12496, 12510, 12519, 12516, 
    12530, 12526, 12531, 12509, 12517, 
    12523, 12532, 12525, 12515, 12529, 
    12503, 13100, 12507, 12522, 12508, 
    12506, 12500, 12499, 12513, 12514, 
    13099, 12521, 12527, 12511, 12512, 
    12528
  ) 
  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.00063

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 (13101,12498,12496,12510,12519,12516,12530,12526,12531,12509,12517,12523,12532,12525,12515,12529,12503,13100,12507,12522,12508,12506,12500,12499,12513,12514,13099,12521,12527,12511,12512,12528)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
12496 0.00000000
12498 0.00000000
12499 0.00000000
12500 0.00000000
12503 0.00000000
12506 0.00000000
12507 0.00000000
12508 0.00000000
12509 0.00000000
12510 0.00000000
12511 0.00000000
12512 0.00000000
12513 0.00000000
12514 0.00000000
12515 0.00000000
12516 0.00000000
12517 0.00000000
12519 0.00000000
12521 0.00000000
12522 0.00000000
12523 0.00000000
12525 0.00000000
12526 0.00000000
12527 0.00000000
12528 0.00000000
12529 0.00000000
12530 0.00000000
12531 0.00000000
12532 0.00000000
13099 0.00000000
13100 0.00000000
13101 0.00000000