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 (
    157878, 
    131244, 
    163555, 
    142795, 
    152834, 
    139304, 
    140470, 
    161677, 
    137204, 
    150431, 
    173467, 
    122494, 
    174303, 
    176633, 
    155460, 
    152788, 
    159979, 
    152387, 
    177902, 
    146542, 
    148127, 
    155163, 
    146233, 
    130037, 
    130745, 
    151021, 
    128287, 
    143111, 
    155514, 
    124685, 
    170152, 
    148944, 
    151780, 
    133106, 
    145497, 
    145180, 
    173179, 
    128213, 
    174680, 
    146818, 
    170999, 
    131696, 
    171673, 
    131951, 
    166209, 
    165386, 
    172481, 
    141152, 
    123413, 
    128274, 
    167042, 
    171994, 
    166489, 
    154484, 
    128340, 
    166263, 
    135458, 
    155044, 
    148326, 
    143307, 
    139706, 
    124396, 
    172334, 
    121219, 
    173565, 
    147677, 
    177514, 
    141410, 
    122327, 
    142464, 
    155085, 
    164296, 
    136827, 
    176060, 
    130512, 
    149075, 
    137444, 
    142067, 
    136299, 
    135240, 
    137144, 
    165641, 
    137493, 
    122588, 
    125079, 
    121137, 
    175250, 
    130713, 
    129259, 
    153437, 
    148372, 
    141211, 
    160027, 
    137205, 
    147933, 
    138696, 
    158978, 
    124484, 
    138587, 
    130862, 
    149784, 
    136011, 
    136213, 
    142966, 
    165523, 
    118369, 
    139067, 
    129348, 
    165171, 
    119815, 
    136217, 
    170004, 
    123533, 
    126063, 
    155865, 
    161288, 
    136896, 
    135083, 
    176947, 
    166994, 
    134428, 
    175167, 
    162335, 
    137156, 
    143428, 
    130486, 
    151548, 
    142017
  ) 
  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.00191

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.525449254,
    "nested_loop": [
      {
        "table": {
          "table_name": "cscart_product_prices",
          "access_type": "range",
          "possible_keys": [
            "usergroup",
            "product_id",
            "lower_limit",
            "usergroup_id",
            "wdbi_product_id_lower_limit_usergroup_id_price_eff1727beeef",
            "wdbi_usergroup_id_lower_limit_product_id_92b7e8f6a250",
            "wdbi_product_id_lower_limit_usergroup_id_price_perc_3b2301d551d0",
            "wdbi_lower_limit_usergroup_id_product_id_b93629431718"
          ],
          "key": "usergroup",
          "key_length": "9",
          "used_key_parts": ["product_id", "usergroup_id", "lower_limit"],
          "loops": 1,
          "rows": 256,
          "cost": 0.44592136,
          "filtered": 20,
          "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.product_id in (157878,131244,163555,142795,152834,139304,140470,161677,137204,150431,173467,122494,174303,176633,155460,152788,159979,152387,177902,146542,148127,155163,146233,130037,130745,151021,128287,143111,155514,124685,170152,148944,151780,133106,145497,145180,173179,128213,174680,146818,170999,131696,171673,131951,166209,165386,172481,141152,123413,128274,167042,171994,166489,154484,128340,166263,135458,155044,148326,143307,139706,124396,172334,121219,173565,147677,177514,141410,122327,142464,155085,164296,136827,176060,130512,149075,137444,142067,136299,135240,137144,165641,137493,122588,125079,121137,175250,130713,129259,153437,148372,141211,160027,137205,147933,138696,158978,124484,138587,130862,149784,136011,136213,142966,165523,118369,139067,129348,165171,119815,136217,170004,123533,126063,155865,161288,136896,135083,176947,166994,134428,175167,162335,137156,143428,130486,151548,142017) and cscart_product_prices.usergroup_id in (0,1)"
        }
      },
      {
        "table": {
          "table_name": "cscart_master_products_storefront_min_price",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "8",
          "used_key_parts": ["product_id", "storefront_id"],
          "ref": [
            "u985510652_ecartify_abhi.cscart_product_prices.product_id",
            "const"
          ],
          "loops": 51.2,
          "rows": 1,
          "cost": 0.046614528,
          "filtered": 100,
          "attached_condition": "trigcond(cscart_master_products_storefront_min_price.product_id = cscart_product_prices.product_id)"
        }
      }
    ]
  }
}