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 (
    13176, 13162, 13169, 13232, 13225, 13190, 
    13183, 13239, 13204, 13197, 13246, 
    13218, 13211, 13253, 13302, 13303, 
    13308, 13309, 13306, 13307, 13310, 
    13311, 13312, 13313, 13304, 13305, 
    13319, 6962, 6966, 6967, 6965, 6963, 
    6952, 6953, 6947, 6959, 3425, 6958, 
    6960, 6968, 6961, 6964, 6957, 6956, 
    6954, 6955, 6948, 6950, 6949, 13298, 
    13299, 13300, 13301, 13124, 13125, 
    13126, 13098, 13105, 13112, 13118, 
    13064, 13071, 13078, 13085, 13091, 
    13314, 13316, 13315, 13317, 13318, 
    12956, 12958, 13014, 13028, 13040, 
    13052, 12960, 12974, 12988, 13000, 
    12926, 12942
  ) 
  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.00208

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "114.81"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "usergroup",
        "used_key_parts": [
          "product_id",
          "usergroup_id",
          "lower_limit"
        ],
        "key_length": "9",
        "rows_examined_per_scan": 164,
        "rows_produced_per_join": 164,
        "filtered": "100.00",
        "index_condition": "((`test2_gaseus`.`cscart_product_prices`.`lower_limit` = 1) and (`test2_gaseus`.`cscart_product_prices`.`product_id` in (13176,13162,13169,13232,13225,13190,13183,13239,13204,13197,13246,13218,13211,13253,13302,13303,13308,13309,13306,13307,13310,13311,13312,13313,13304,13305,13319,6962,6966,6967,6965,6963,6952,6953,6947,6959,3425,6958,6960,6968,6961,6964,6957,6956,6954,6955,6948,6950,6949,13298,13299,13300,13301,13124,13125,13126,13098,13105,13112,13118,13064,13071,13078,13085,13091,13314,13316,13315,13317,13318,12956,12958,13014,13028,13040,13052,12960,12974,12988,13000,12926,12942)) and (`test2_gaseus`.`cscart_product_prices`.`usergroup_id` in (0,1)))",
        "cost_info": {
          "read_cost": "98.41",
          "eval_cost": "16.40",
          "prefix_cost": "114.81",
          "data_read_per_join": "3K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ]
      }
    }
  }
}

Result

product_id price
3425 1480.54000000
6947 198.13000000
6948 168.70000000
6949 192.20000000
6950 214.05000000
6952 402.61000000
6953 596.24000000
6954 175.84000000
6955 175.84000000
6956 251.32000000
6957 251.32000000
6958 1483.13000000
6959 1462.13000000
6960 1748.00000000
6961 2614.57000000
6962 2607.56000000
6963 2170.24000000
6964 2434.98000000
6965 2170.24000000
6966 2163.15000000
6967 2329.50000000
6968 2434.98000000
12926 679.00000000
12942 696.00000000
12956 1610.00000000
12958 2380.00000000
12960 639.00000000
12974 987.00000000
12988 1090.00000000
13000 985.00000000
13014 1200.00000000
13028 1430.00000000
13040 1540.00000000
13052 1680.00000000
13064 1400.00000000
13071 1420.00000000
13078 1530.00000000
13085 2030.00000000
13091 1760.00000000
13098 2010.00000000
13105 2150.00000000
13112 2430.00000000
13118 2160.00000000
13124 2320.00000000
13125 2920.00000000
13126 6830.00000000
13162 2470.00000000
13169 2470.00000000
13176 2010.00000000
13183 2680.00000000
13190 2010.00000000
13197 3160.00000000
13204 2700.00000000
13211 3570.00000000
13218 3100.00000000
13225 3370.00000000
13232 3360.00000000
13239 3890.00000000
13246 4060.00000000
13253 4390.00000000
13298 535.00000000
13299 535.00000000
13300 455.00000000
13301 455.00000000
13302 109.00000000
13303 109.00000000
13304 102.00000000
13305 102.00000000
13306 75.00000000
13307 75.00000000
13308 166.00000000
13309 174.00000000
13310 193.00000000
13311 140.00000000
13312 157.00000000
13313 157.00000000
13314 89.30000000
13315 228.00000000
13316 228.00000000
13317 209.00000000
13318 209.00000000
13319 53.70000000