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 (
    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, 12648, 12611, 
    12596, 12607, 12608, 12610, 12643, 
    12649, 12609, 12644, 12650, 12652, 
    12653, 12654, 12646, 12655, 12656, 
    12645, 12651, 12597, 12615, 12603, 
    12629, 12604
  ) 
  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.00260

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": 58,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (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,12648,12611,12596,12607,12608,12610,12643,12649,12609,12644,12650,12652,12653,12654,12646,12655,12656,12645,12651,12597,12615,12603,12629,12604)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
12551 15215.00000000
12552 14984.00000000
12554 19331.00000000
12585 209523.67000000
12587 209523.67000000
12588 6750.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
12627 21943.06000000
12628 21943.06000000
12629 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
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
13091 6750.00000000
15800 33378.35000000