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 (
    13018, 
    13017, 
    13016, 
    13015, 
    13014, 
    13013, 
    13012, 
    13011, 
    13010, 
    13009, 
    13008, 
    13007, 
    13006, 
    13005, 
    13004, 
    13003, 
    13002, 
    13001, 
    13000, 
    12999, 
    10812, 
    10811, 
    10810, 
    10809, 
    10808, 
    10807, 
    10806, 
    10805, 
    10804, 
    10803, 
    10802, 
    10801, 
    10800, 
    10799, 
    10798, 
    10797, 
    10796, 
    10795, 
    10794, 
    10793, 
    10792, 
    10791, 
    10790, 
    10789, 
    10788, 
    10787, 
    10786, 
    10785, 
    10784, 
    10783, 
    10782, 
    10781, 
    10780, 
    10779, 
    10778, 
    10777, 
    10776, 
    10775, 
    10774, 
    10773, 
    10772, 
    10771, 
    10770, 
    10769, 
    10768, 
    10767, 
    10766, 
    10765, 
    10764, 
    10763, 
    10762, 
    10761, 
    10760, 
    10759, 
    10758, 
    10757, 
    10756, 
    10755, 
    10754, 
    10753, 
    10752, 
    10751, 
    10750, 
    10749, 
    10748, 
    10747, 
    15736, 
    15737, 
    15738, 
    15739, 
    15740, 
    15741, 
    15742, 
    15743, 
    15745, 
    15746, 
    15744, 
    12632, 
    12633, 
    10179, 
    10180, 
    10523, 
    10073, 
    10077, 
    10076, 
    10086, 
    10085, 
    10084, 
    10083, 
    10082, 
    10081, 
    10080, 
    10079, 
    10584, 
    10594, 
    12574, 
    10168, 
    10167, 
    12703, 
    15968, 
    15938, 
    10053, 
    10041, 
    9904, 
    9905, 
    15966, 
    15965, 
    9940
  ) 
  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.01000

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": 192,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (13018,13017,13016,13015,13014,13013,13012,13011,13010,13009,13008,13007,13006,13005,13004,13003,13002,13001,13000,12999,10812,10811,10810,10809,10808,10807,10806,10805,10804,10803,10802,10801,10800,10799,10798,10797,10796,10795,10794,10793,10792,10791,10790,10789,10788,10787,10786,10785,10784,10783,10782,10781,10780,10779,10778,10777,10776,10775,10774,10773,10772,10771,10770,10769,10768,10767,10766,10765,10764,10763,10762,10761,10760,10759,10758,10757,10756,10755,10754,10753,10752,10751,10750,10749,10748,10747,15736,15737,15738,15739,15740,15741,15742,15743,15745,15746,15744,12632,12633,10179,10180,10523,10073,10077,10076,10086,10085,10084,10083,10082,10081,10080,10079,10584,10594,12574,10168,10167,12703,15968,15938,10053,10041,9904,9905,15966,15965,9940)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9904 1849.73000000
9905 1849.73000000
9940 2142.00000000
10041 1849.73000000
10053 1955.00000000
10073 1660.30000000
10076 1660.30000000
10077 1660.30000000
10079 1660.30000000
10080 1660.30000000
10081 1660.30000000
10082 1660.30000000
10083 1660.30000000
10084 1660.30000000
10085 1660.30000000
10086 1660.30000000
10167 1849.73000000
10168 1849.73000000
10179 1660.30000000
10180 1660.30000000
10523 1660.30000000
10584 1660.30000000
10594 1660.30000000
10747 6184.31000000
10748 6184.31000000
10749 6184.31000000
10750 6184.31000000
10751 6184.31000000
10752 6184.31000000
10753 7644.03000000
10754 7644.03000000
10755 7644.03000000
10756 7644.03000000
10757 7644.03000000
10758 7644.03000000
10759 3264.87000000
10760 3264.87000000
10761 3264.87000000
10762 3264.87000000
10763 3264.87000000
10764 3264.87000000
10765 3264.87000000
10766 3264.87000000
10767 5972.60000000
10768 5972.60000000
10769 5972.60000000
10770 5972.60000000
10771 5972.60000000
10772 5972.60000000
10773 5972.60000000
10774 5972.60000000
10775 5972.60000000
10776 5972.60000000
10777 5972.60000000
10778 2106.01000000
10779 2106.01000000
10780 2106.01000000
10781 2106.01000000
10782 2106.01000000
10783 2106.01000000
10784 2106.01000000
10785 2106.01000000
10786 2106.01000000
10787 2106.01000000
10788 2106.01000000
10789 2106.01000000
10790 2106.01000000
10791 2106.01000000
10792 1582.30000000
10793 1582.30000000
10794 1582.30000000
10795 1582.30000000
10796 1582.30000000
10797 1582.30000000
10798 1582.30000000
10799 1582.30000000
10800 1582.30000000
10801 1582.30000000
10802 1582.30000000
10803 1582.30000000
10804 1582.30000000
10805 1582.30000000
10806 1582.30000000
10807 1582.30000000
10808 1582.30000000
10809 1582.30000000
10810 1582.30000000
10811 1582.30000000
10812 1582.30000000
12574 78181.27000000
12632 67629.15000000
12633 67629.15000000
12703 1849.73000000
12999 2061.44000000
13000 2061.44000000
13001 2061.44000000
13002 2061.44000000
13003 2061.44000000
13004 2061.44000000
13005 2061.44000000
13006 2061.44000000
13007 2061.44000000
13008 2061.44000000
13009 2061.44000000
13010 2061.44000000
13011 2061.44000000
13012 2061.44000000
13013 1927.73000000
13014 1927.73000000
13015 1927.73000000
13016 1927.73000000
13017 1927.73000000
13018 1927.73000000
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
15938 4889.00000000
15965 6424.00000000
15966 5975.00000000
15968 4889.00000000