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 (
    187, 
    321, 
    323, 
    326, 
    329, 
    332, 
    16, 
    5, 
    24, 
    170, 
    1, 
    22, 
    149, 
    227, 
    78, 
    79, 
    190, 
    189, 
    162, 
    159, 
    158, 
    245, 
    238, 
    239, 
    242, 
    243, 
    240, 
    214, 
    217, 
    218, 
    207, 
    59, 
    58, 
    60, 
    199, 
    198, 
    94, 
    96, 
    201, 
    114, 
    192, 
    136, 
    130, 
    128, 
    126, 
    131, 
    177, 
    186, 
    185, 
    179, 
    11, 
    172, 
    134, 
    135, 
    195, 
    233, 
    168, 
    167, 
    169, 
    42, 
    39, 
    40, 
    287, 
    205, 
    37, 
    152, 
    160, 
    74, 
    73, 
    71, 
    75, 
    72, 
    108, 
    237, 
    191, 
    235, 
    70, 
    122, 
    115, 
    156, 
    90, 
    175, 
    196, 
    43, 
    104, 
    76, 
    129, 
    127, 
    121, 
    103, 
    95, 
    182, 
    41, 
    56, 
    57, 
    30, 
    112, 
    246, 
    89, 
    197, 
    208, 
    173, 
    97, 
    229, 
    211, 
    210, 
    209, 
    67, 
    25, 
    116, 
    213, 
    215, 
    226, 
    200, 
    154, 
    53, 
    52, 
    111, 
    82, 
    84, 
    228, 
    85, 
    88, 
    26, 
    35, 
    47, 
    81, 
    107
  ) 
  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.00176

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "350.94"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "256.00"
      },
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "ALL",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "rows_examined_per_scan": 454,
        "rows_produced_per_join": 255,
        "filtered": "56.39",
        "cost_info": {
          "read_cost": "43.74",
          "eval_cost": "51.20",
          "prefix_cost": "94.94",
          "data_read_per_join": "5K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`atulecarter_atul_demo2`.`cscart_product_prices`.`lower_limit` = 1) and (`atulecarter_atul_demo2`.`cscart_product_prices`.`product_id` in (187,321,323,326,329,332,16,5,24,170,1,22,149,227,78,79,190,189,162,159,158,245,238,239,242,243,240,214,217,218,207,59,58,60,199,198,94,96,201,114,192,136,130,128,126,131,177,186,185,179,11,172,134,135,195,233,168,167,169,42,39,40,287,205,37,152,160,74,73,71,75,72,108,237,191,235,70,122,115,156,90,175,196,43,104,76,129,127,121,103,95,182,41,56,57,30,112,246,89,197,208,173,97,229,211,210,209,67,25,116,213,215,226,200,154,53,52,111,82,84,228,85,88,26,35,47,81,107)) and (`atulecarter_atul_demo2`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
1 5399.99000000
5 899.99000000
11 30.00000000
16 349.99000000
22 799.99000000
24 449.99000000
25 599.99000000
26 34.99000000
30 349.95000000
35 31.99000000
37 159.95000000
39 419.00000000
40 229.00000000
41 35.00000000
42 79.00000000
43 369.00000000
47 29.99000000
52 139.99000000
53 38.99000000
56 220.00000000
57 309.00000000
58 779.00000000
59 599.00000000
60 1499.00000000
67 339.99000000
70 499.99000000
71 529.99000000
72 599.99000000
73 589.99000000
74 524.99000000
75 489.99000000
76 439.99000000
78 100.00000000
79 96.00000000
81 49.50000000
82 19.99000000
84 19.99000000
85 19.99000000
88 39.99000000
89 19.99000000
90 19.99000000
94 59.99000000
95 19.99000000
96 99.99000000
97 14.99000000
103 23.99000000
104 29.95000000
107 465.00000000
108 12.99000000
111 6.99000000
112 8.99000000
114 14.99000000
115 4595.00000000
116 6.99000000
121 17.99000000
122 5695.00000000
126 129.99000000
127 4779.00000000
128 17.99000000
129 1799.00000000
130 49.95000000
131 1249.00000000
134 89.99000000
135 0.00000000
136 0.00000000
149 53.99000000
152 700.00000000
154 399.99000000
156 299.00000000
158 4750.00000000
159 11375.00000000
160 200.00000000
162 32750.00000000
167 549.95000000
168 1.00000000
169 749.95000000
170 145.99000000
172 299.99000000
173 349.99000000
175 179.99000000
177 400.00000000
179 299.97000000
182 6.80000000
185 139.99000000
186 299.99000000
187 299.99000000
189 1.00000000
190 899.95000000
191 11.98000000
192 15.00000000
195 29.99000000
196 17.00000000
197 14.98000000
198 17.99000000
199 29.98000000
200 26.92000000
201 12.67000000
205 149.99000000
207 42.00000000
208 82.94000000
209 109.99000000
210 89.99000000
211 299.99000000
213 295.00000000
214 972.00000000
215 1095.00000000
217 610.99000000
218 459.99000000
226 269.99000000
227 699.00000000
228 349.99000000
229 299.99000000
233 47.99000000
235 79.99000000
237 299.99000000
238 499.99000000
239 509.99000000
240 499.00000000
242 249.00000000
243 249.00000000
245 699.00000000
246 399.99000000
287 1200.00000000
321 299.99000000
323 299.99000000
326 299.99000000
329 299.99000000
332 299.99000000