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.00181

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