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 (
    82038, 148882, 148883, 148884, 148885, 
    148886, 148887, 148888, 148889, 148890, 
    148891, 148892, 148893, 148894, 148895, 
    148896, 148897, 148898, 148899, 148900, 
    148901, 148902, 148903, 148904, 148905, 
    148906, 148907, 148908, 148909, 148910, 
    148911, 148912, 148913, 148914, 148915, 
    148916, 148917, 148918, 148919, 148920, 
    148921, 148922, 148923, 148924, 148925, 
    148926, 148927, 148928, 148929, 148930, 
    148931
  ) 
  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.00159

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 (82038,148882,148883,148884,148885,148886,148887,148888,148889,148890,148891,148892,148893,148894,148895,148896,148897,148898,148899,148900,148901,148902,148903,148904,148905,148906,148907,148908,148909,148910,148911,148912,148913,148914,148915,148916,148917,148918,148919,148920,148921,148922,148923,148924,148925,148926,148927,148928,148929,148930,148931)) and (`webmarco`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id	price
82038	99.00000000
148882	99.00000000
148883	99.00000000
148884	99.00000000
148885	99.00000000
148886	99.00000000
148887	99.00000000
148888	99.00000000
148889	99.00000000
148890	99.00000000
148891	99.00000000
148892	99.00000000
148893	99.00000000
148894	99.00000000
148895	99.00000000
148896	99.00000000
148897	99.00000000
148898	99.00000000
148899	99.00000000
148900	99.00000000
148901	99.00000000
148902	99.00000000
148903	99.00000000
148904	99.00000000
148905	99.00000000
148906	99.00000000
148907	99.00000000
148908	99.00000000
148909	99.00000000
148910	99.00000000
148911	99.00000000
148912	99.00000000
148913	99.00000000
148914	99.00000000
148915	99.00000000
148916	99.00000000
148917	99.00000000
148918	99.00000000
148919	99.00000000
148920	99.00000000
148921	99.00000000
148922	99.00000000
148923	99.00000000
148924	99.00000000
148925	99.00000000
148926	99.00000000
148927	99.00000000
148928	99.00000000
148929	99.00000000
148930	99.00000000
148931	99.00000000