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 (
    153378, 
    146250, 
    151676, 
    170573, 
    132911, 
    151528, 
    168300, 
    146008, 
    149505, 
    137270, 
    118463, 
    175108, 
    120360, 
    125752, 
    145670, 
    165520, 
    164728, 
    168291, 
    3459, 
    135232, 
    134222, 
    163348, 
    147116, 
    141352, 
    173196, 
    133367, 
    136278, 
    164224, 
    169761, 
    120371, 
    126781, 
    174454, 
    159721, 
    122698, 
    146691, 
    132361, 
    145504, 
    151584, 
    163029, 
    142300, 
    148407, 
    159680, 
    166851, 
    135515, 
    158366, 
    167558, 
    118856, 
    150294, 
    120895, 
    164939, 
    148060, 
    170707, 
    154297, 
    169846, 
    159381, 
    165473, 
    152983, 
    168016, 
    159560, 
    157441, 
    160602, 
    125438, 
    174205, 
    125076, 
    141675, 
    141823, 
    120656, 
    126863, 
    158696, 
    145139, 
    137529, 
    141871, 
    139050, 
    175166, 
    171127, 
    120496, 
    169901, 
    138735, 
    146824, 
    158471, 
    162470, 
    154893, 
    146279, 
    121442, 
    125787, 
    120264, 
    138776, 
    164451, 
    138985, 
    121657, 
    139237, 
    172341, 
    169627, 
    121067, 
    122178, 
    131028, 
    133688, 
    135620, 
    129139, 
    156331, 
    177744, 
    160951, 
    160534, 
    171706, 
    128069, 
    163441, 
    131982, 
    170708, 
    136526, 
    152889, 
    146907, 
    169481, 
    172066, 
    165323, 
    120928, 
    143092, 
    160218, 
    146004, 
    169267, 
    151452, 
    165666, 
    149523, 
    131594, 
    129845, 
    133281, 
    148051, 
    163097, 
    144228
  ) 
  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.00220

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 (153378,146250,151676,170573,132911,151528,168300,146008,149505,137270,118463,175108,120360,125752,145670,165520,164728,168291,3459,135232,134222,163348,147116,141352,173196,133367,136278,164224,169761,120371,126781,174454,159721,122698,146691,132361,145504,151584,163029,142300,148407,159680,166851,135515,158366,167558,118856,150294,120895,164939,148060,170707,154297,169846,159381,165473,152983,168016,159560,157441,160602,125438,174205,125076,141675,141823,120656,126863,158696,145139,137529,141871,139050,175166,171127,120496,169901,138735,146824,158471,162470,154893,146279,121442,125787,120264,138776,164451,138985,121657,139237,172341,169627,121067,122178,131028,133688,135620,129139,156331,177744,160951,160534,171706,128069,163441,131982,170708,136526,152889,146907,169481,172066,165323,120928,143092,160218,146004,169267,151452,165666,149523,131594,129845,133281,148051,163097,144228) 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)"
        }
      }
    ]
  }
}