SELECT 
  cscart_ult_product_prices.product_id, 
  MIN(
    IF(
      cscart_ult_product_prices.percentage_discount = 0, 
      cscart_ult_product_prices.price, 
      cscart_ult_product_prices.price - (
        cscart_ult_product_prices.price * cscart_ult_product_prices.percentage_discount
      )/ 100
    )
  ) AS price 
FROM 
  cscart_ult_product_prices 
WHERE 
  cscart_ult_product_prices.product_id IN (
    154, 53, 52, 228, 161, 165, 166, 86, 106, 
    105, 247, 101, 33, 32, 163, 244, 212, 
    155, 55, 69, 206, 184, 183, 133, 132, 
    125, 120, 223, 225, 224, 146, 147, 145, 
    117, 219, 222, 220, 221, 113, 157, 68, 
    29, 6, 8, 14, 9, 10, 19, 21, 50, 51, 49, 
    48, 54, 153, 164, 248, 65, 64, 63, 62, 
    66, 140, 141
  ) 
  AND cscart_ult_product_prices.lower_limit = 1 
  AND cscart_ult_product_prices.company_id = 1 
  AND cscart_ult_product_prices.usergroup_id IN (0, 0, 1) 
GROUP BY 
  cscart_ult_product_prices.product_id

Query time 0.00164

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "60.00"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "table": {
        "table_name": "cscart_ult_product_prices",
        "access_type": "ref",
        "possible_keys": [
          "usergroup",
          "product_id",
          "company_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "company_id",
        "used_key_parts": [
          "company_id"
        ],
        "key_length": "4",
        "ref": [
          "const"
        ],
        "rows_examined_per_scan": 240,
        "rows_produced_per_join": 16,
        "filtered": "6.96",
        "index_condition": "(`rajansecarter_single_store_19_jan`.`cscart_ult_product_prices`.`company_id` <=> 1)",
        "cost_info": {
          "read_cost": "12.00",
          "eval_cost": "3.34",
          "prefix_cost": "60.00",
          "data_read_per_join": "534"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "company_id",
          "usergroup_id"
        ],
        "attached_condition": "((`rajansecarter_single_store_19_jan`.`cscart_ult_product_prices`.`lower_limit` = 1) and (`rajansecarter_single_store_19_jan`.`cscart_ult_product_prices`.`product_id` in (154,53,52,228,161,165,166,86,106,105,247,101,33,32,163,244,212,155,55,69,206,184,183,133,132,125,120,223,225,224,146,147,145,117,219,222,220,221,113,157,68,29,6,8,14,9,10,19,21,50,51,49,48,54,153,164,248,65,64,63,62,66,140,141)) and (`rajansecarter_single_store_19_jan`.`cscart_ult_product_prices`.`usergroup_id` in (0,0,1)))"
      }
    }
  }
}

Result

product_id price
6 329.990000
8 849.990000
9 999.990000
10 1199.990000
14 499.990000
19 79.990000
21 29.990000
29 199.950000
32 299.990000
33 169.990000
48 180.000000
49 120.000000
50 220.000000
51 180.000000
52 139.990000
53 38.990000
54 269.000000
55 359.000000
62 209.950000
63 189.950000
64 189.950000
65 99.950000
66 389.950000
68 799.990000
69 529.990000
86 359.000000
101 188.880000
105 169.990000
106 179.990000
113 449.990000
117 729.990000
120 199.990000
125 149.990000
132 269.990000
133 229.990000
140 99.950000
141 99.950000
145 79.990000
146 44.990000
147 44.990000
153 49.990000
154 399.990000
155 79.990000
157 499.000000
161 279.990000
163 899.990000
164 249.990000
165 599.950000
166 749.950000
183 249.990000
184 299.990000
206 179.990000
212 129.950000
219 529.990000
220 1099.990000
221 2049.000000
222 529.990000
223 499.990000
224 479.990000
225 199.990000
228 349.990000
244 729.990000
247 329.490000
248 372.270000