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 (
    4002, 4003, 4004, 4005, 4059, 4060, 4061, 
    4062, 4063, 4064, 4065, 4142, 4143, 
    4144, 4145, 4146, 4147, 4148, 4195, 
    4196, 4197, 4198, 4199, 4200, 4201, 
    6716, 6715, 3117, 3119, 3127, 3133, 
    3141, 3142, 6674, 6680, 6692, 6693, 
    6701, 6707, 1417, 1393, 1394, 1395, 
    1396, 1397, 1398, 1399, 1400
  ) 
  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.00098

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.61"
    },
    "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": 48,
        "rows_produced_per_join": 9,
        "filtered": "20.00",
        "index_condition": "(`gaseus`.`cscart_product_prices`.`product_id` in (4002,4003,4004,4005,4059,4060,4061,4062,4063,4064,4065,4142,4143,4144,4145,4146,4147,4148,4195,4196,4197,4198,4199,4200,4201,6716,6715,3117,3119,3127,3133,3141,3142,6674,6680,6692,6693,6701,6707,1417,1393,1394,1395,1396,1397,1398,1399,1400))",
        "cost_info": {
          "read_cost": "32.65",
          "eval_cost": "0.96",
          "prefix_cost": "33.61",
          "data_read_per_join": "230"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`gaseus`.`cscart_product_prices`.`lower_limit` = 1) and (`gaseus`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
1393 0.00000000
1394 0.00000000
1395 0.00000000
1396 0.00000000
1397 0.00000000
1398 0.00000000
1399 0.00000000
1400 0.00000000
1417 0.00000000
3117 390.00000000
3119 390.00000000
3127 390.00000000
3133 390.00000000
3141 390.00000000
3142 390.00000000
4002 0.00000000
4003 0.00000000
4004 0.00000000
4005 0.00000000
4059 0.00000000
4060 0.00000000
4061 0.00000000
4062 0.00000000
4063 0.00000000
4064 0.00000000
4065 0.00000000
4142 0.00000000
4143 0.00000000
4144 0.00000000
4145 0.00000000
4146 0.00000000
4147 0.00000000
4148 0.00000000
4195 0.00000000
4196 0.00000000
4197 0.00000000
4198 0.00000000
4199 0.00000000
4200 0.00000000
4201 0.00000000
6674 390.00000000
6680 390.00000000
6692 575.00000000
6693 390.00000000
6701 535.00000000
6707 535.00000000
6715 390.00000000
6716 390.00000000