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 (
    201, 202, 271, 272, 273, 274, 275, 276, 
    277, 325, 326, 327, 328, 329, 330, 415, 
    416, 417, 418, 3616, 3617, 3618, 4080, 
    4081, 4082, 4083, 4084, 4085, 4086, 
    4156, 4157, 4158, 4159, 4160, 4161, 
    4162, 4209, 4210, 4211, 4212, 4213, 
    4214, 4215, 3102, 3110, 6670, 6685, 
    6698
  ) 
  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.00138

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 (201,202,271,272,273,274,275,276,277,325,326,327,328,329,330,415,416,417,418,3616,3617,3618,4080,4081,4082,4083,4084,4085,4086,4156,4157,4158,4159,4160,4161,4162,4209,4210,4211,4212,4213,4214,4215,3102,3110,6670,6685,6698))",
        "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
201 0.00000000
202 0.00000000
271 0.00000000
272 0.00000000
273 0.00000000
274 0.00000000
275 0.00000000
276 0.00000000
277 0.00000000
325 0.00000000
326 0.00000000
327 0.00000000
328 0.00000000
329 0.00000000
330 0.00000000
415 0.00000000
416 0.00000000
417 0.00000000
418 0.00000000
3102 618.00000000
3110 618.00000000
3616 0.00000000
3617 0.00000000
3618 0.00000000
4080 0.00000000
4081 0.00000000
4082 0.00000000
4083 0.00000000
4084 0.00000000
4085 0.00000000
4086 0.00000000
4156 0.00000000
4157 0.00000000
4158 0.00000000
4159 0.00000000
4160 0.00000000
4161 0.00000000
4162 0.00000000
4209 0.00000000
4210 0.00000000
4211 0.00000000
4212 0.00000000
4213 0.00000000
4214 0.00000000
4215 0.00000000
6670 696.00000000
6685 618.00000000
6698 618.00000000