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 (
    5504, 5384, 5506, 6018, 6749, 5385, 5464, 
    6185, 6735, 6114, 5647, 6713, 6146, 
    6187, 6742, 5651, 6086, 6299, 6307, 
    6371, 5508, 6081, 8812, 6740, 6705, 
    6144, 6254, 6433, 6755, 6228, 6192, 
    6227, 6356, 6083, 6230, 6710, 6275, 
    6277, 6393, 6426, 6258, 6428, 8800, 
    6255, 6394, 6229, 6256, 8576, 6189, 
    6257, 6259, 6145, 6332, 6362, 8577, 
    6278, 5644, 6226, 6279, 6392, 6297, 
    6354, 6331, 6337, 6395, 6423, 6427, 
    6364, 8569, 6190, 6276, 6303, 6359, 
    6429, 6280, 6260, 6305, 6330, 6434, 
    6355
  ) 
  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.00277

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "56.01"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "product_id",
        "used_key_parts": [
          "product_id"
        ],
        "key_length": "3",
        "rows_examined_per_scan": 80,
        "rows_produced_per_join": 15,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (5504,5384,5506,6018,6749,5385,5464,6185,6735,6114,5647,6713,6146,6187,6742,5651,6086,6299,6307,6371,5508,6081,8812,6740,6705,6144,6254,6433,6755,6228,6192,6227,6356,6083,6230,6710,6275,6277,6393,6426,6258,6428,8800,6255,6394,6229,6256,8576,6189,6257,6259,6145,6332,6362,8577,6278,5644,6226,6279,6392,6297,6354,6331,6337,6395,6423,6427,6364,8569,6190,6276,6303,6359,6429,6280,6260,6305,6330,6434,6355))",
        "cost_info": {
          "read_cost": "54.41",
          "eval_cost": "1.60",
          "prefix_cost": "56.01",
          "data_read_per_join": "383"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`lower_limit` = 1) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
5384 170000.00000000
5385 125000.00000000
5464 101500.00000000
5504 80000.00000000
5506 92000.00000000
5508 170000.00000000
5644 103500.00000000
5647 108000.00000000
5651 85000.00000000
6018 78000.00000000
6081 128000.00000000
6083 118000.00000000
6086 176000.00000000
6114 125000.00000000
6144 192000.00000000
6145 251000.00000000
6146 52000.00000000
6185 60000.00000000
6187 60000.00000000
6189 117500.00000000
6190 124500.00000000
6192 62000.00000000
6226 22000.00000000
6227 39000.00000000
6228 65000.00000000
6229 62000.00000000
6230 96000.00000000
6254 95000.00000000
6255 116500.00000000
6256 108500.00000000
6257 97500.00000000
6258 83000.00000000
6259 108000.00000000
6260 57000.00000000
6275 54000.00000000
6276 54000.00000000
6277 79500.00000000
6278 124000.00000000
6279 95000.00000000
6280 136000.00000000
6297 85000.00000000
6299 85000.00000000
6303 66000.00000000
6305 53500.00000000
6307 156000.00000000
6330 680000.00000000
6331 525000.00000000
6332 567000.00000000
6337 321000.00000000
6354 483000.00000000
6355 358000.00000000
6356 318000.00000000
6359 405000.00000000
6362 154500.00000000
6364 240000.00000000
6371 107000.00000000
6392 122000.00000000
6393 102500.00000000
6394 86000.00000000
6395 145000.00000000
6423 65000.00000000
6426 98750.00000000
6427 139250.00000000
6428 171250.00000000
6429 216250.00000000
6433 209000.00000000
6434 53750.00000000
6705 269000.00000000
6710 399000.00000000
6713 812000.00000000
6735 406000.00000000
6740 979000.00000000
6742 392000.00000000
6749 211000.00000000
6755 472000.00000000
8569 18000.00000000
8576 19000.00000000
8577 23000.00000000
8800 14000.00000000
8812 38000.00000000