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 (
    13101, 
    12498, 
    12496, 
    12510, 
    12519, 
    12516, 
    12530, 
    12526, 
    12531, 
    12509, 
    12517, 
    12523, 
    12532, 
    12525, 
    12515, 
    12529, 
    12503, 
    13100, 
    12507, 
    12522, 
    12508, 
    12506, 
    12500, 
    12499, 
    12513, 
    12514, 
    13099, 
    12521, 
    12527, 
    12511, 
    12512, 
    12528, 
    12520, 
    12497, 
    12505, 
    12502, 
    12504, 
    10847, 
    13152, 
    10846, 
    10845, 
    10844, 
    10843, 
    10842, 
    10841, 
    10840, 
    10839, 
    10838, 
    10837, 
    10836, 
    10835, 
    10834, 
    10833, 
    10832, 
    10831, 
    10830, 
    10829, 
    10828, 
    10827, 
    12642, 
    12648, 
    12651, 
    12645, 
    12644, 
    12650, 
    12649, 
    12643, 
    12640, 
    12647, 
    15800, 
    12639, 
    12641, 
    12638, 
    12637, 
    12634, 
    12635, 
    12636, 
    12654, 
    12653, 
    12652, 
    12656, 
    12655, 
    12646, 
    12631, 
    12998, 
    12997, 
    12996, 
    12995, 
    10826, 
    10825, 
    10824, 
    10823, 
    10822, 
    10821, 
    10820, 
    10819, 
    10818, 
    10817, 
    10816, 
    10815, 
    10814, 
    10813, 
    13044, 
    13043, 
    13042, 
    13041, 
    13040, 
    13039, 
    13038, 
    13037, 
    13036, 
    13035, 
    13034, 
    13033, 
    13032, 
    13031, 
    13030, 
    13029, 
    13028, 
    13027, 
    13026, 
    13025, 
    13024, 
    13023, 
    13022, 
    13021, 
    13020, 
    13019
  ) 
  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.00192

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": 128,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (13101,12498,12496,12510,12519,12516,12530,12526,12531,12509,12517,12523,12532,12525,12515,12529,12503,13100,12507,12522,12508,12506,12500,12499,12513,12514,13099,12521,12527,12511,12512,12528,12520,12497,12505,12502,12504,10847,13152,10846,10845,10844,10843,10842,10841,10840,10839,10838,10837,10836,10835,10834,10833,10832,10831,10830,10829,10828,10827,12642,12648,12651,12645,12644,12650,12649,12643,12640,12647,15800,12639,12641,12638,12637,12634,12635,12636,12654,12653,12652,12656,12655,12646,12631,12998,12997,12996,12995,10826,10825,10824,10823,10822,10821,10820,10819,10818,10817,10816,10815,10814,10813,13044,13043,13042,13041,13040,13039,13038,13037,13036,13035,13034,13033,13032,13031,13030,13029,13028,13027,13026,13025,13024,13023,13022,13021,13020,13019)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
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
10827 1582.30000000
10828 1582.30000000
10829 3053.16000000
10830 3053.16000000
10831 3053.16000000
10832 3053.16000000
10833 3053.16000000
10834 3053.16000000
10835 3053.16000000
10836 3053.16000000
10837 3053.16000000
10838 3053.16000000
10839 3053.16000000
10840 3877.73000000
10841 3877.73000000
10842 3877.73000000
10843 3877.73000000
10844 3877.73000000
10845 3877.73000000
10846 3877.73000000
10847 3877.73000000
12496 0.00000000
12497 0.00000000
12498 0.00000000
12499 0.00000000
12500 0.00000000
12502 0.00000000
12503 0.00000000
12504 0.00000000
12505 0.00000000
12506 0.00000000
12507 0.00000000
12508 0.00000000
12509 0.00000000
12510 0.00000000
12511 0.00000000
12512 0.00000000
12513 0.00000000
12514 0.00000000
12515 0.00000000
12516 0.00000000
12517 0.00000000
12519 0.00000000
12520 0.00000000
12521 0.00000000
12522 0.00000000
12523 0.00000000
12525 0.00000000
12526 0.00000000
12527 0.00000000
12528 0.00000000
12529 0.00000000
12530 0.00000000
12531 0.00000000
12532 0.00000000
12631 41728.72000000
12634 25398.80000000
12635 25398.80000000
12636 46725.90000000
12637 19650.51000000
12638 19593.76000000
12639 19593.76000000
12640 19593.76000000
12641 19593.76000000
12642 19593.76000000
12643 19593.76000000
12644 19593.76000000
12645 19593.76000000
12646 23593.03000000
12647 33378.35000000
12648 33378.35000000
12649 33378.35000000
12650 33378.35000000
12651 33378.35000000
12652 23593.03000000
12653 43410.89000000
12654 70782.80000000
12655 43410.89000000
12656 70782.80000000
12995 3186.87000000
12996 3186.87000000
12997 3186.87000000
12998 3186.87000000
13019 1927.73000000
13020 1927.73000000
13021 1927.73000000
13022 1927.73000000
13023 1927.73000000
13024 1927.73000000
13025 1927.73000000
13026 1927.73000000
13027 1927.73000000
13028 1927.73000000
13029 1927.73000000
13030 1927.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
13099 0.00000000
13100 0.00000000
13101 0.00000000
13152 3877.73000000
15800 33378.35000000