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 (
    93, 94, 95, 96, 154, 155, 156, 157, 158, 
    159, 160, 175, 176, 177, 178, 179, 180, 
    181, 257, 258, 259, 260, 261, 262, 263, 
    311, 312, 313, 314, 315, 316, 3539, 3540, 
    3541, 3542, 3543, 3544, 3545, 3954, 
    3955, 3956, 3957, 3958, 3959, 3960, 
    3999, 4000, 4001
  ) 
  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.00095

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 (93,94,95,96,154,155,156,157,158,159,160,175,176,177,178,179,180,181,257,258,259,260,261,262,263,311,312,313,314,315,316,3539,3540,3541,3542,3543,3544,3545,3954,3955,3956,3957,3958,3959,3960,3999,4000,4001))",
        "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
93 0.00000000
94 0.00000000
95 0.00000000
96 0.00000000
154 0.00000000
155 0.00000000
156 0.00000000
157 0.00000000
158 0.00000000
159 0.00000000
160 0.00000000
175 0.00000000
176 0.00000000
177 0.00000000
178 0.00000000
179 0.00000000
180 0.00000000
181 0.00000000
257 0.00000000
258 0.00000000
259 0.00000000
260 0.00000000
261 0.00000000
262 0.00000000
263 0.00000000
311 0.00000000
312 0.00000000
313 0.00000000
314 0.00000000
315 0.00000000
316 0.00000000
3539 0.00000000
3540 0.00000000
3541 0.00000000
3542 0.00000000
3543 0.00000000
3544 0.00000000
3545 0.00000000
3954 0.00000000
3955 0.00000000
3956 0.00000000
3957 0.00000000
3958 0.00000000
3959 0.00000000
3960 0.00000000
3999 0.00000000
4000 0.00000000
4001 0.00000000