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, 1154, 3315, 3318, 3687, 5076, 
    4691, 3071, 513, 3505, 3319, 3306, 3296, 
    1172, 1173, 4917, 4918, 6962, 6966, 
    6967, 6965, 6963
  ) 
  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.00149

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "67.21"
    },
    "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": 96,
        "rows_produced_per_join": 96,
        "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,1154,3315,3318,3687,5076,4691,3071,513,3505,3319,3306,3296,1172,1173,4917,4918,6962,6966,6967,6965,6963)) and (`test2_gaseus`.`cscart_product_prices`.`usergroup_id` in (0,1)))",
        "cost_info": {
          "read_cost": "57.61",
          "eval_cost": "9.60",
          "prefix_cost": "67.21",
          "data_read_per_join": "2K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ]
      }
    }
  }
}

Result

product_id price
513 0.00000000
1154 0.00000000
1172 0.00000000
1173 0.00000000
3071 0.00000000
3296 140.00000000
3306 132.00000000
3315 724.00000000
3318 100.00000000
3319 253.00000000
3505 0.00000000
3687 0.00000000
4691 0.00000000
4917 0.00000000
4918 0.00000000
5076 0.00000000
6962 2607.56000000
6963 2170.24000000
6965 2170.24000000
6966 2163.15000000
6967 2329.50000000
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
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
13319 53.70000000