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 (
    5696, 
    5621, 
    5892, 
    5719, 
    5706, 
    5720, 
    5703, 
    4894, 
    4962, 
    5691, 
    5687, 
    3733, 
    4915, 
    3589, 
    4976, 
    3811, 
    6093, 
    6194, 
    4935, 
    3734, 
    3641, 
    5700, 
    6064, 
    3798, 
    4931, 
    3730, 
    3638, 
    3729, 
    3851, 
    6204, 
    3942, 
    3732, 
    3448, 
    3728, 
    3852, 
    3850, 
    3731, 
    3642, 
    3943, 
    3623, 
    3645, 
    3637, 
    3950, 
    6074, 
    3644, 
    3864, 
    3940, 
    3865, 
    6117, 
    3869, 
    3948, 
    3941, 
    6068, 
    3951, 
    5812, 
    3949, 
    6097, 
    8612, 
    6073, 
    8464, 
    8469, 
    10025, 
    8724, 
    8445, 
    8811, 
    8441, 
    8446, 
    8440, 
    8593, 
    8595, 
    8444, 
    8471, 
    8594, 
    8457, 
    8828, 
    8466, 
    8831, 
    8276, 
    8365, 
    8585, 
    8615, 
    8729, 
    8730, 
    8797, 
    8810, 
    8814, 
    8829, 
    8244, 
    8277, 
    8377, 
    8403, 
    8431, 
    8587, 
    8798, 
    8271, 
    8274, 
    8278, 
    8333, 
    8389, 
    8430, 
    8516, 
    8518, 
    8537, 
    8755, 
    10044, 
    8334, 
    8383, 
    8462, 
    8539, 
    10046, 
    8401, 
    8386, 
    11588, 
    11597, 
    11608, 
    11580, 
    11609, 
    11589, 
    11591, 
    11596, 
    11604, 
    11611, 
    11582, 
    11584, 
    11586, 
    11595, 
    11606, 
    11603
  ) 
  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.00159

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "107.16"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "product_id",
        "used_key_parts": [
          "product_id"
        ],
        "key_length": "3",
        "rows_examined_per_scan": 167,
        "rows_produced_per_join": 33,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (5696,5621,5892,5719,5706,5720,5703,4894,4962,5691,5687,3733,4915,3589,4976,3811,6093,6194,4935,3734,3641,5700,6064,3798,4931,3730,3638,3729,3851,6204,3942,3732,3448,3728,3852,3850,3731,3642,3943,3623,3645,3637,3950,6074,3644,3864,3940,3865,6117,3869,3948,3941,6068,3951,5812,3949,6097,8612,6073,8464,8469,10025,8724,8445,8811,8441,8446,8440,8593,8595,8444,8471,8594,8457,8828,8466,8831,8276,8365,8585,8615,8729,8730,8797,8810,8814,8829,8244,8277,8377,8403,8431,8587,8798,8271,8274,8278,8333,8389,8430,8516,8518,8537,8755,10044,8334,8383,8462,8539,10046,8401,8386,11588,11597,11608,11580,11609,11589,11591,11596,11604,11611,11582,11584,11586,11595,11606,11603))",
        "cost_info": {
          "read_cost": "103.82",
          "eval_cost": "3.34",
          "prefix_cost": "107.16",
          "data_read_per_join": "801"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`lower_limit` = 1) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}