Sandbox

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 (
    149042, 149050, 149051, 149052, 149053, 
    149054, 149055, 149056, 149057, 149058, 
    149059, 149060, 149061, 149062, 149063, 
    149064, 149065, 149066, 149067, 149068, 
    149069, 149070, 149071, 149072, 149073, 
    149074, 149075, 149076, 149077, 149078, 
    149079, 149080, 149081, 149082, 149083, 
    149084, 149085, 149086, 149087, 149088, 
    149089, 149090, 149091, 149092, 149093, 
    149094, 149095, 149096, 149097, 149098, 
    149099
  ) 
  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.00187

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "142.96"
    },
    "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": 102,
        "rows_produced_per_join": 102,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "122.56",
          "eval_cost": "20.40",
          "prefix_cost": "142.96",
          "data_read_per_join": "6K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`webmarco`.`cscart_product_prices`.`lower_limit` = 1) and (`webmarco`.`cscart_product_prices`.`product_id` in (149042,149050,149051,149052,149053,149054,149055,149056,149057,149058,149059,149060,149061,149062,149063,149064,149065,149066,149067,149068,149069,149070,149071,149072,149073,149074,149075,149076,149077,149078,149079,149080,149081,149082,149083,149084,149085,149086,149087,149088,149089,149090,149091,149092,149093,149094,149095,149096,149097,149098,149099)) and (`webmarco`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id	price
149042	99.00000000
149050	99.00000000
149051	99.00000000
149052	99.00000000
149053	99.00000000
149054	99.00000000
149055	99.00000000
149056	99.00000000
149057	99.00000000
149058	99.00000000
149059	99.00000000
149060	99.00000000
149061	99.00000000
149062	99.00000000
149063	99.00000000
149064	99.00000000
149065	99.00000000
149066	99.00000000
149067	99.00000000
149068	99.00000000
149069	99.00000000
149070	99.00000000
149071	99.00000000
149072	99.00000000
149073	99.00000000
149074	99.00000000
149075	99.00000000
149076	99.00000000
149077	99.00000000
149078	99.00000000
149079	99.00000000
149080	99.00000000
149081	99.00000000
149082	99.00000000
149083	99.00000000
149084	99.00000000
149085	99.00000000
149086	99.00000000
149087	99.00000000
149088	99.00000000
149089	99.00000000
149090	99.00000000
149091	99.00000000
149092	99.00000000
149093	99.00000000
149094	99.00000000
149095	99.00000000
149096	99.00000000
149097	99.00000000
149098	99.00000000
149099	99.00000000