SELECT 
  cscart_product_prices.product_id, 
  COALESCE(
    cscart_master_products_storefront_min_price.price, 
    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 
  LEFT JOIN cscart_master_products_storefront_min_price ON cscart_master_products_storefront_min_price.product_id = cscart_product_prices.product_id 
  AND cscart_master_products_storefront_min_price.storefront_id = 1 
WHERE 
  cscart_product_prices.product_id IN (
    10613, 10612, 10611, 15515, 15516, 18081, 
    18082, 18083, 10002, 7503, 10610, 10609, 
    10608, 10607, 11908, 18086, 18087, 
    10007, 10063, 10062, 10064, 9997, 10000, 
    9999
  ) 
  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.00121

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_master_products_storefront_min_price",
      "access_type": "system",
      "possible_keys": ["PRIMARY"],
      "rows": 0,
      "filtered": 0,
      "const_row_not_found": true
    },
    "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": 24,
      "filtered": 75,
      "index_condition": "cscart_product_prices.product_id in (10613,10612,10611,15515,15516,18081,18082,18083,10002,7503,10610,10609,10608,10607,11908,18086,18087,10007,10063,10062,10064,9997,10000,9999)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
7503 3290.00000000
9997 3290.00000000
9999 3290.00000000
10000 3290.00000000
10002 2990.00000000
10007 1590.00000000
10062 1590.00000000
10063 1590.00000000
10064 1590.00000000
10607 2990.00000000
10608 2990.00000000
10609 2990.00000000
10610 3290.00000000
10611 2590.00000000
10612 2590.00000000
10613 2590.00000000
11908 4990.00000000
15515 2990.00000000
15516 2990.00000000
18081 2990.00000000
18082 2990.00000000
18083 2990.00000000
18086 2190.00000000
18087 2190.00000000