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 (
    227, 241, 240, 214, 217, 218, 207, 229, 
    213, 215, 226, 228, 247, 244, 225, 224, 
    223, 206, 205, 208, 211, 210, 212, 222, 
    219, 220, 221, 209
  ) 
  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.00169

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": 40.17447281,
          "index_condition": "cscart_product_prices.product_id in (227,241,240,214,217,218,207,229,213,215,226,228,247,244,225,224,223,206,205,208,211,210,212,222,219,220,221,209)",
          "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
        }
      }
    ]
  }
}

Result

product_id price
205 5999.60000000
206 7199.60000000
207 1680.00000000
208 3317.60000000
209 4399.60000000
210 3599.60000000
211 11999.60000000
212 5198.00000000
213 11800.00000000
214 38880.00000000
215 43800.00000000
217 24439.60000000
218 18399.60000000
219 21199.60000000
220 43999.60000000
221 81960.00000000
222 21199.60000000
223 19999.60000000
224 19199.60000000
225 7999.60000000
226 10799.60000000
227 27960.00000000
228 13999.60000000
229 11999.60000000
240 19960.00000000
241 19960.00000000
244 29199.60000000
247 13179.60000000