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 (
    10556, 
    10101, 
    10558, 
    10142, 
    10134, 
    10141, 
    10053, 
    10138, 
    10130, 
    9934, 
    10563, 
    10569, 
    10572, 
    10602, 
    10603, 
    10137, 
    12517, 
    12531, 
    12526, 
    12509, 
    10226, 
    10551, 
    10550, 
    10193, 
    10659, 
    10664, 
    10666, 
    10661, 
    10663, 
    10190, 
    10657, 
    10658, 
    10194, 
    10692, 
    12530, 
    12516, 
    12496, 
    12519, 
    12510, 
    10087, 
    10100, 
    10698, 
    10478, 
    9883, 
    9901, 
    10493, 
    10485, 
    10476, 
    10479, 
    10494, 
    10492, 
    10468, 
    9903, 
    10487, 
    9899, 
    10473, 
    10486, 
    9881, 
    9896, 
    9892, 
    10481, 
    9876, 
    10474, 
    10491, 
    10489, 
    10471, 
    9880, 
    10472, 
    9885, 
    10488, 
    9897, 
    9895, 
    10467, 
    9900, 
    10470, 
    10466, 
    10477, 
    9889, 
    10475, 
    9890, 
    10483, 
    10495, 
    10490, 
    10469, 
    9877, 
    9894, 
    9891, 
    10480, 
    9893, 
    9878, 
    9886, 
    9882, 
    9888, 
    10482, 
    13091, 
    12551, 
    12638, 
    12634, 
    12636, 
    12635, 
    12599, 
    12627, 
    12600, 
    12639, 
    15800, 
    12595, 
    12587, 
    12593, 
    12620, 
    12623, 
    12619, 
    12585, 
    12616, 
    12601, 
    12628, 
    12602, 
    12640, 
    12647, 
    12588, 
    12605, 
    12606, 
    12637, 
    12612, 
    12613, 
    12641, 
    12554, 
    12552, 
    12642
  ) 
  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.00093

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 (10556,10101,10558,10142,10134,10141,10053,10138,10130,9934,10563,10569,10572,10602,10603,10137,12517,12531,12526,12509,10226,10551,10550,10193,10659,10664,10666,10661,10663,10190,10657,10658,10194,10692,12530,12516,12496,12519,12510,10087,10100,10698,10478,9883,9901,10493,10485,10476,10479,10494,10492,10468,9903,10487,9899,10473,10486,9881,9896,9892,10481,9876,10474,10491,10489,10471,9880,10472,9885,10488,9897,9895,10467,9900,10470,10466,10477,9889,10475,9890,10483,10495,10490,10469,9877,9894,9891,10480,9893,9878,9886,9882,9888,10482,13091,12551,12638,12634,12636,12635,12599,12627,12600,12639,15800,12595,12587,12593,12620,12623,12619,12585,12616,12601,12628,12602,12640,12647,12588,12605,12606,12637,12612,12613,12641,12554,12552,12642)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9876 1036.29000000
9877 1036.29000000
9878 1036.29000000
9880 1036.29000000
9881 1036.29000000
9882 1036.29000000
9883 1036.29000000
9885 1036.29000000
9886 1036.29000000
9888 1036.29000000
9889 1036.29000000
9890 1036.29000000
9891 1036.29000000
9892 1036.29000000
9893 1036.29000000
9894 1036.29000000
9895 1036.29000000
9896 1036.29000000
9897 1036.29000000
9899 1036.29000000
9900 1036.29000000
9901 1036.29000000
9903 1036.29000000
9934 2039.00000000
10053 1955.00000000
10087 1635.00000000
10100 1635.00000000
10101 2405.00000000
10130 2121.00000000
10134 2121.00000000
10137 1924.00000000
10138 1955.00000000
10141 2153.00000000
10142 2121.00000000
10190 2257.00000000
10193 2325.00000000
10194 2257.00000000
10226 1524.00000000
10466 1036.29000000
10467 1036.29000000
10468 1036.29000000
10469 1036.29000000
10470 1036.29000000
10471 1036.29000000
10472 1036.29000000
10473 1036.29000000
10474 1036.29000000
10475 1036.29000000
10476 1036.29000000
10477 1036.29000000
10478 1036.29000000
10479 1036.29000000
10480 1036.29000000
10481 1036.29000000
10482 1036.29000000
10483 1036.29000000
10485 1036.29000000
10486 1036.29000000
10487 1036.29000000
10488 1036.29000000
10489 1036.29000000
10490 1036.29000000
10491 1036.29000000
10492 1036.29000000
10493 1036.29000000
10494 1036.29000000
10495 1036.29000000
10550 1995.00000000
10551 1995.00000000
10556 2658.00000000
10558 2658.00000000
10563 2039.00000000
10569 2325.00000000
10572 2325.00000000
10602 1885.00000000
10603 2517.00000000
10657 2671.00000000
10658 2671.00000000
10659 2686.00000000
10661 2262.00000000
10663 2262.00000000
10664 2325.00000000
10666 2325.00000000
10692 2671.00000000
10698 1635.00000000
12496 0.00000000
12509 0.00000000
12510 0.00000000
12516 0.00000000
12517 0.00000000
12519 0.00000000
12526 0.00000000
12530 0.00000000
12531 0.00000000
12551 15215.00000000
12552 14984.00000000
12554 19331.00000000
12585 209523.67000000
12587 209523.67000000
12588 6750.00000000
12593 349146.47000000
12595 131354.16000000
12599 14092.68000000
12600 4597.28000000
12601 14092.68000000
12602 4597.28000000
12605 14092.68000000
12606 4597.28000000
12612 15852.84000000
12613 15852.84000000
12616 15852.84000000
12619 131354.16000000
12620 131354.16000000
12623 209523.67000000
12627 21943.06000000
12628 21943.06000000
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
12647 33378.35000000
13091 6750.00000000
15800 33378.35000000