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 (
    2222, 2223, 5353, 5354, 5355, 5356, 5357, 
    5358, 5359, 5360, 5361, 5362, 5363, 
    5364, 5365, 5366, 5367, 5368, 5369, 
    5370, 5820, 5821, 5822, 5823, 3135, 
    5802, 6970, 6971, 6972, 6973, 6974, 
    6975, 3403, 3404, 3405, 3406, 3408, 
    3409, 6930, 6931, 6932, 6933, 6934, 
    6935, 6936, 3407, 2666, 3845
  ) 
  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.00103

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 (2222,2223,5353,5354,5355,5356,5357,5358,5359,5360,5361,5362,5363,5364,5365,5366,5367,5368,5369,5370,5820,5821,5822,5823,3135,5802,6970,6971,6972,6973,6974,6975,3403,3404,3405,3406,3408,3409,6930,6931,6932,6933,6934,6935,6936,3407,2666,3845))",
        "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
2222 0.00000000
2223 0.00000000
2666 0.00000000
3135 0.00000000
3403 282.00000000
3404 282.00000000
3405 282.00000000
3406 564.00000000
3407 564.00000000
3408 564.00000000
3409 564.00000000
3845 0.00000000
5353 0.00000000
5354 0.00000000
5355 0.00000000
5356 0.00000000
5357 0.00000000
5358 0.00000000
5359 0.00000000
5360 0.00000000
5361 0.00000000
5362 0.00000000
5363 0.00000000
5364 0.00000000
5365 0.00000000
5366 0.00000000
5367 0.00000000
5368 0.00000000
5369 0.00000000
5370 0.00000000
5802 0.00000000
5820 0.00000000
5821 0.00000000
5822 0.00000000
5823 0.00000000
6930 564.00000000
6931 282.00000000
6932 282.00000000
6933 282.00000000
6934 564.00000000
6935 564.00000000
6936 564.00000000
6970 0.00000000
6971 0.00000000
6972 0.00000000
6973 0.00000000
6974 0.00000000
6975 0.00000000