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 (
    10705, 
    10706, 
    10707, 
    10708, 
    10709, 
    10710, 
    10711, 
    10712, 
    10668, 
    10669, 
    10670, 
    10671, 
    10672, 
    10673, 
    10674, 
    10675, 
    10676, 
    10677, 
    10678, 
    10679, 
    10680, 
    10681, 
    10682, 
    10683, 
    10684, 
    10685, 
    10686, 
    10725, 
    10724, 
    10701, 
    10699, 
    10698, 
    10696, 
    10695, 
    10693, 
    10692, 
    15958, 
    15957, 
    15956, 
    12600, 
    12602, 
    12604, 
    12606, 
    12608, 
    12603, 
    12615, 
    12597, 
    12609, 
    12610, 
    12607, 
    12596, 
    12611, 
    12613, 
    12612, 
    12605, 
    12601, 
    12616, 
    12599, 
    10666, 
    10664, 
    10663, 
    10661, 
    10659, 
    10658, 
    10657, 
    12619, 
    12620, 
    12593, 
    12595, 
    12585, 
    12587, 
    12623, 
    13091, 
    12588, 
    12590, 
    12591, 
    12592, 
    10497, 
    10498, 
    10499, 
    10500, 
    10501, 
    10502, 
    10503, 
    10504, 
    10505, 
    10506, 
    10507, 
    10508, 
    10509, 
    10510, 
    10511, 
    10512, 
    10513, 
    10514, 
    10515, 
    10516, 
    10517, 
    10518, 
    10519, 
    10520, 
    10521, 
    10641, 
    10640, 
    10639, 
    10638, 
    10637, 
    10636, 
    10635, 
    10634, 
    10633, 
    10632, 
    10631, 
    10630, 
    10629, 
    10628, 
    10627, 
    10626, 
    10625, 
    10624, 
    10623, 
    10622, 
    10621, 
    10620, 
    10619, 
    10618, 
    10617, 
    10616
  ) 
  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.00177

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 (10705,10706,10707,10708,10709,10710,10711,10712,10668,10669,10670,10671,10672,10673,10674,10675,10676,10677,10678,10679,10680,10681,10682,10683,10684,10685,10686,10725,10724,10701,10699,10698,10696,10695,10693,10692,15958,15957,15956,12600,12602,12604,12606,12608,12603,12615,12597,12609,12610,12607,12596,12611,12613,12612,12605,12601,12616,12599,10666,10664,10663,10661,10659,10658,10657,12619,12620,12593,12595,12585,12587,12623,13091,12588,12590,12591,12592,10497,10498,10499,10500,10501,10502,10503,10504,10505,10506,10507,10508,10509,10510,10511,10512,10513,10514,10515,10516,10517,10518,10519,10520,10521,10641,10640,10639,10638,10637,10636,10635,10634,10633,10632,10631,10630,10629,10628,10627,10626,10625,10624,10623,10622,10621,10620,10619,10618,10617,10616)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10497 3599.16000000
10498 3599.16000000
10499 3599.16000000
10500 3599.16000000
10501 3599.16000000
10502 4033.73000000
10503 4033.73000000
10504 4033.73000000
10505 4033.73000000
10506 4033.73000000
10507 4033.73000000
10508 4033.73000000
10509 4189.73000000
10510 4189.73000000
10511 4189.73000000
10512 4189.73000000
10513 4189.73000000
10514 4189.73000000
10515 4189.73000000
10516 4189.73000000
10517 4189.73000000
10518 3365.16000000
10519 3365.16000000
10520 3365.16000000
10521 3365.16000000
10616 1292.58000000
10617 1292.58000000
10618 1292.58000000
10619 1292.58000000
10620 1292.58000000
10621 1292.58000000
10622 1292.58000000
10623 1292.58000000
10624 1292.58000000
10625 1582.30000000
10626 1582.30000000
10627 1582.30000000
10628 1582.30000000
10629 1582.30000000
10630 1582.30000000
10631 1582.30000000
10632 1582.30000000
10633 1582.30000000
10634 1582.30000000
10635 1582.30000000
10636 1582.30000000
10637 2540.59000000
10638 2540.59000000
10639 2540.59000000
10640 2540.59000000
10641 2540.59000000
10657 2671.00000000
10658 2671.00000000
10659 2686.00000000
10661 2262.00000000
10663 2262.00000000
10664 2325.00000000
10666 2325.00000000
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
10692 2671.00000000
10693 2089.00000000
10695 1848.00000000
10696 1840.00000000
10698 1635.00000000
10699 1551.00000000
10701 1840.00000000
10705 3432.02000000
10706 3432.02000000
10707 3432.02000000
10708 3432.02000000
10709 3432.02000000
10710 3432.02000000
10711 3432.02000000
10712 3432.02000000
10724 2206.30000000
10725 2206.30000000
12585 209523.67000000
12587 209523.67000000
12588 6750.00000000
12590 67629.15000000
12591 67629.15000000
12592 64680.00000000
12593 349146.47000000
12595 131354.16000000
12596 45140.72000000
12597 46725.90000000
12599 14092.68000000
12600 4597.28000000
12601 14092.68000000
12602 4597.28000000
12603 14092.68000000
12604 4597.28000000
12605 14092.68000000
12606 4597.28000000
12607 14092.68000000
12608 2296.27000000
12609 14678.09000000
12610 13507.47000000
12611 13507.47000000
12612 15852.84000000
12613 15852.84000000
12615 15852.84000000
12616 15852.84000000
12619 131354.16000000
12620 131354.16000000
12623 209523.67000000
13091 6750.00000000
15956 6896.00000000
15957 4709.00000000
15958 5578.00000000