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 (
    13008, 13009, 13010, 13011, 13012, 9874, 
    9875, 9887, 9879, 9796, 9942, 9943, 
    9941, 9944, 10813, 10814, 10815, 10816, 
    10817, 10818, 10819, 10820, 10821, 
    10822, 10823, 10824, 10825, 10826, 
    10704, 10705, 10706, 10707
  ) 
  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.00061

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 (13008,13009,13010,13011,13012,9874,9875,9887,9879,9796,9942,9943,9941,9944,10813,10814,10815,10816,10817,10818,10819,10820,10821,10822,10823,10824,10825,10826,10704,10705,10706,10707)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9796 1704.87000000
9874 1704.87000000
9875 1704.87000000
9879 1704.87000000
9887 1704.87000000
9941 1704.87000000
9942 1704.87000000
9943 1704.87000000
9944 1704.87000000
10704 3432.02000000
10705 3432.02000000
10706 3432.02000000
10707 3432.02000000
10813 1704.87000000
10814 1704.87000000
10815 1704.87000000
10816 1704.87000000
10817 1704.87000000
10818 1704.87000000
10819 1704.87000000
10820 1704.87000000
10821 1704.87000000
10822 1704.87000000
10823 1704.87000000
10824 1704.87000000
10825 1704.87000000
10826 1704.87000000
13008 2061.44000000
13009 2061.44000000
13010 2061.44000000
13011 2061.44000000
13012 2061.44000000