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 (
    10194, 10692, 12530, 12516, 12496, 12519, 
    12510, 10087, 10100, 10698, 10478, 
    9883, 9901, 10493, 10485, 10476, 10479, 
    10494, 10492, 10468, 9903, 10487, 9899, 
    10473, 10486, 9881, 9896, 9892, 10481, 
    9876, 10474, 10491
  ) 
  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.00056

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 (10194,10692,12530,12516,12496,12519,12510,10087,10100,10698,10478,9883,9901,10493,10485,10476,10479,10494,10492,10468,9903,10487,9899,10473,10486,9881,9896,9892,10481,9876,10474,10491)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9876 1036.29000000
9881 1036.29000000
9883 1036.29000000
9892 1036.29000000
9896 1036.29000000
9899 1036.29000000
9901 1036.29000000
9903 1036.29000000
10087 1635.00000000
10100 1635.00000000
10194 2257.00000000
10468 1036.29000000
10473 1036.29000000
10474 1036.29000000
10476 1036.29000000
10478 1036.29000000
10479 1036.29000000
10481 1036.29000000
10485 1036.29000000
10486 1036.29000000
10487 1036.29000000
10491 1036.29000000
10492 1036.29000000
10493 1036.29000000
10494 1036.29000000
10692 2671.00000000
10698 1635.00000000
12496 0.00000000
12510 0.00000000
12516 0.00000000
12519 0.00000000
12530 0.00000000