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 (
    9790, 9787, 9791, 9792, 9795, 9784, 9785, 
    9783, 9786, 9788, 13031, 13032, 13033, 
    13034, 13035, 13036, 13037, 13038, 
    13039, 13040, 13041, 13042, 13043, 
    13044, 10840, 10841, 10842, 10843, 
    10844, 10845, 10846, 10847
  ) 
  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.00075

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 (9790,9787,9791,9792,9795,9784,9785,9783,9786,9788,13031,13032,13033,13034,13035,13036,13037,13038,13039,13040,13041,13042,13043,13044,10840,10841,10842,10843,10844,10845,10846,10847)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9783 1292.58000000
9784 1292.58000000
9785 1292.58000000
9786 1292.58000000
9787 1292.58000000
9788 1292.58000000
9790 1292.58000000
9791 1292.58000000
9792 1292.58000000
9795 1292.58000000
10840 3877.73000000
10841 3877.73000000
10842 3877.73000000
10843 3877.73000000
10844 3877.73000000
10845 3877.73000000
10846 3877.73000000
10847 3877.73000000
13031 2106.01000000
13032 2106.01000000
13033 2106.01000000
13034 2106.01000000
13035 2106.01000000
13036 2106.01000000
13037 2106.01000000
13038 2106.01000000
13039 2106.01000000
13040 2106.01000000
13041 2106.01000000
13042 2106.01000000
13043 2106.01000000
13044 2106.01000000