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