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 (
    66397, 66398, 66547, 66548, 66556, 66838, 
    67040, 68613, 68682, 68956, 69097, 
    69107, 2006, 5522, 5584, 5587, 5879, 
    5880, 5881, 5882, 5884, 5890, 6105, 
    6111
  ) 
  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.00069

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "23.11"
    },
    "grouping_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_master_products_storefront_min_price",
            "access_type": "system",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows_examined_per_scan": 0,
            "rows_produced_per_join": 1,
            "filtered": "0.00",
            "const_row_not_found": true,
            "cost_info": {
              "read_cost": "0.00",
              "eval_cost": "0.10",
              "prefix_cost": "0.00",
              "data_read_per_join": "16"
            },
            "used_columns": [
              "storefront_id",
              "product_id",
              "price"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_product_prices",
            "access_type": "range",
            "possible_keys": [
              "usergroup",
              "product_id",
              "lower_limit",
              "usergroup_id",
              "idx_product_prices_usergroup_limit"
            ],
            "key": "product_id",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 38,
            "rows_produced_per_join": 7,
            "filtered": "19.99",
            "index_condition": "(`cscart_db`.`cscart_product_prices`.`product_id` in (66397,66398,66547,66548,66556,66838,67040,68613,68682,68956,69097,69107,2006,5522,5584,5587,5879,5880,5881,5882,5884,5890,6105,6111))",
            "cost_info": {
              "read_cost": "22.35",
              "eval_cost": "0.76",
              "prefix_cost": "23.11",
              "data_read_per_join": "182"
            },
            "used_columns": [
              "product_id",
              "price",
              "percentage_discount",
              "lower_limit",
              "usergroup_id"
            ],
            "attached_condition": "((`cscart_db`.`cscart_product_prices`.`lower_limit` = 1) and (`cscart_db`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
          }
        }
      ]
    }
  }
}

Result

product_id price
2006 4196.000000
5522 995.000000
5584 700.000000
5587 489.300000
5879 385.000000
5880 700.000000
5881 559.300000
5882 1119.300000
5884 489.300000
5890 419.300000
6105 475.000000
6111 845.000000
66397 840.000000
66398 840.000000
66547 210.000000
66548 210.000000
66556 210.000000
66838 100.000000
67040 1231.650000
68613 239.200000
68682 315.000000
68956 180.700000
69097 199.200000
69107 304.000000