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 (
    176, 196, 175, 189, 187, 195, 186, 185, 
    193, 167, 178, 179, 190, 180, 181, 169, 
    165, 171, 159, 177, 739, 738, 1185, 790, 
    799, 1842, 796, 994, 972, 971, 314, 261, 
    673, 275, 1029, 925, 1027, 276, 331, 
    328, 811, 810, 1039, 1038, 1040, 233, 
    231, 232, 243, 246, 244, 242, 245, 240, 
    290, 508, 295, 286, 259, 260, 249, 716, 
    719, 211, 253, 239, 93, 262, 325, 221, 
    809, 254, 1237, 271, 1009, 264, 725, 
    250, 266, 235, 873, 872, 875, 874, 666, 
    300, 669, 664, 309, 287, 288, 668, 667, 
    308, 662, 663
  ) 
  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.00309

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_product_prices",
      "access_type": "range",
      "possible_keys": ["usergroup", "product_id", "lower_limit", "usergroup_id"],
      "key": "product_id",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "rows": 96,
      "filtered": 19.59183121,
      "index_condition": "cscart_product_prices.product_id in (176,196,175,189,187,195,186,185,193,167,178,179,190,180,181,169,165,171,159,177,739,738,1185,790,799,1842,796,994,972,971,314,261,673,275,1029,925,1027,276,331,328,811,810,1039,1038,1040,233,231,232,243,246,244,242,245,240,290,508,295,286,259,260,249,716,719,211,253,239,93,262,325,221,809,254,1237,271,1009,264,725,250,266,235,873,872,875,874,666,300,669,664,309,287,288,668,667,308,662,663)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
93 35000.00000000
159 0.00000000
165 0.00000000
167 0.00000000
169 0.00000000
171 0.00000000
175 0.00000000
176 0.00000000
177 0.00000000
178 0.00000000
179 0.00000000
180 0.00000000
181 0.00000000
185 0.00000000
186 0.00000000
187 18000.00000000
189 0.00000000
190 0.00000000
193 0.00000000
195 0.00000000
196 0.00000000
211 151200.00000000
221 0.00000000
231 43200.00000000
232 43200.00000000
233 43200.00000000
235 78544.00000000
239 49200.00000000
240 68400.00000000
242 58800.00000000
243 62400.00000000
244 62400.00000000
245 62400.00000000
246 62400.00000000
249 32400.00000000
250 199200.00000000
253 48000.00000000
254 72540.00000000
259 0.00000000
260 0.00000000
261 0.00000000
262 0.00000000
264 93600.00000000
266 69600.00000000
271 0.00000000
275 0.00000000
276 0.00000000
286 58800.00000000
287 30000.00000000
288 26400.00000000
290 57000.00000000
295 50400.00000000
300 37000.00000000
308 0.00000000
309 27600.00000000
314 28000.00000000
325 97200.00000000
328 48000.00000000
331 48000.00000000
508 6000.00000000
662 27600.00000000
663 27600.00000000
664 0.00000000
666 0.00000000
667 0.00000000
668 0.00000000
669 0.00000000
673 200608.00000000
716 162000.00000000
719 163200.00000000
725 66000.00000000
738 32000.00000000
739 0.00000000
790 30000.00000000
796 11000.00000000
799 16000.00000000
809 71220.00000000
810 24360.00000000
811 24360.00000000
872 0.00000000
873 0.00000000
874 0.00000000
875 0.00000000
925 0.00000000
971 0.00000000
972 0.00000000
994 0.00000000
1009 86400.00000000
1027 28000.00000000
1029 32000.00000000
1038 41000.00000000
1039 51360.00000000
1040 36000.00000000
1185 26000.00000000
1237 28000.00000000
1842 23500.00000000