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 (
    9984, 9985, 9986, 9987, 9988, 9989, 9990, 
    9991, 9992, 9993, 9994, 9995, 9996, 
    9997, 9998, 9999, 12995, 12996, 12997, 
    12998, 10734, 10735, 10736, 10737, 
    10738, 10747, 10748, 10749, 10750, 
    10751, 10752, 15744, 15745, 15746, 
    15743, 15738, 15739, 15740, 15741, 
    15742, 15736, 15737, 10668, 10669, 
    10670, 10671, 10672, 10673, 10674, 
    10675, 10676, 10677, 10678, 10679, 
    10680, 10681, 10682, 10683, 10684, 
    10685, 10686, 10607, 10608, 10609
  ) 
  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.00130

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": 64,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (9984,9985,9986,9987,9988,9989,9990,9991,9992,9993,9994,9995,9996,9997,9998,9999,12995,12996,12997,12998,10734,10735,10736,10737,10738,10747,10748,10749,10750,10751,10752,15744,15745,15746,15743,15738,15739,15740,15741,15742,15736,15737,10668,10669,10670,10671,10672,10673,10674,10675,10676,10677,10678,10679,10680,10681,10682,10683,10684,10685,10686,10607,10608,10609)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9984 1241.41000000
9985 1241.41000000
9986 1241.41000000
9987 1241.41000000
9988 1241.41000000
9989 1241.41000000
9990 1241.41000000
9991 1241.41000000
9992 1241.41000000
9993 1241.41000000
9994 1241.41000000
9995 1241.41000000
9996 1241.41000000
9997 1241.41000000
9998 1241.41000000
9999 1241.41000000
10607 1292.58000000
10608 1292.58000000
10609 1292.58000000
10668 1136.58000000
10669 1136.58000000
10670 1136.58000000
10671 1136.58000000
10672 1136.58000000
10673 1136.58000000
10674 1136.58000000
10675 1136.58000000
10676 1136.58000000
10677 1136.58000000
10678 1136.58000000
10679 1136.58000000
10680 1136.58000000
10681 1136.58000000
10682 1136.58000000
10683 1136.58000000
10684 1136.58000000
10685 1136.58000000
10686 1136.58000000
10734 2294.40000000
10735 2294.40000000
10736 2294.40000000
10737 2294.40000000
10738 2294.40000000
10747 6184.31000000
10748 6184.31000000
10749 6184.31000000
10750 6184.31000000
10751 6184.31000000
10752 6184.31000000
12995 3186.87000000
12996 3186.87000000
12997 3186.87000000
12998 3186.87000000
15736 3955.73000000
15737 3955.73000000
15738 3955.73000000
15739 3955.73000000
15740 3955.73000000
15741 3955.73000000
15742 3955.73000000
15743 3955.73000000
15744 3000.00000000
15745 3955.73000000
15746 3955.73000000