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 (
    138, 139, 140, 141, 142, 143, 144, 145, 
    146, 147, 148, 149, 150, 151, 152, 153, 
    154, 155, 156, 157, 160, 161, 163, 164, 
    165, 167, 168, 169, 170, 172, 173, 175
  ) 
  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.00127

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "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": 32,
          "filtered": 45.91368103,
          "index_condition": "cscart_product_prices.product_id in (138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,160,161,163,164,165,167,168,169,170,172,173,175)",
          "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
        }
      }
    ]
  }
}

Result

product_id price
138 0.00000000
139 0.00000000
140 3998.00000000
141 3998.00000000
142 15998.00000000
143 3198.00000000
144 3998.00000000
145 3199.60000000
146 1799.60000000
147 1799.60000000
148 5200.00000000
149 2159.60000000
150 4800.00000000
151 20000.00000000
152 28000.00000000
153 1999.60000000
154 15999.60000000
155 3199.60000000
156 11960.00000000
157 19960.00000000
160 8000.00000000
161 11199.60000000
163 35999.60000000
164 3749.65000000
165 9998.25000000
167 19248.25000000
168 40.00000000
169 29998.00000000
170 5839.60000000
172 11999.60000000
173 13999.60000000
175 7199.60000000