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)))"
      }
    }
  }
}

Result

product_id price
3448 12000.00000000
3589 47000.00000000
3623 12000.00000000
3637 12000.00000000
3638 12000.00000000
3641 9000.00000000
3642 9000.00000000
3644 9000.00000000
3645 9000.00000000
3728 7500.00000000
3729 7500.00000000
3730 7500.00000000
3731 7500.00000000
3732 7500.00000000
3733 7500.00000000
3734 7500.00000000
3798 5000.00000000
3811 8000.00000000
3850 6000.00000000
3851 6000.00000000
3852 6000.00000000
3864 4500.00000000
3865 4500.00000000
3869 10000.00000000
3940 10000.00000000
3941 10000.00000000
3942 18000.00000000
3943 18000.00000000
3948 25000.00000000
3949 25000.00000000
3950 25000.00000000
3951 25000.00000000
4894 70000.00000000
4915 70000.00000000
4931 70000.00000000
4935 30000.00000000
4962 70000.00000000
4976 70000.00000000
5621 120000.00000000
5687 120000.00000000
5691 148000.00000000
5696 88000.00000000
5700 120000.00000000
5703 148000.00000000
5706 70000.00000000
5719 70000.00000000
5720 70000.00000000
5812 40000.00000000
5892 120000.00000000
6064 30000.00000000
6068 40000.00000000
6073 148000.00000000
6074 40000.00000000
6093 82000.00000000
6097 88000.00000000
6117 60000.00000000
6194 70000.00000000
6204 40000.00000000
8244 29000.00000000
8271 29000.00000000
8274 18000.00000000
8276 14000.00000000
8277 29000.00000000
8278 21000.00000000
8333 21000.00000000
8334 29000.00000000
8365 21000.00000000
8377 24000.00000000
8383 29000.00000000
8386 18000.00000000
8389 21000.00000000
8401 21000.00000000
8403 12000.00000000
8430 12000.00000000
8431 12000.00000000
8440 35000.00000000
8441 35000.00000000
8444 35000.00000000
8445 35000.00000000
8446 35000.00000000
8457 35000.00000000
8462 35000.00000000
8464 35000.00000000
8466 39000.00000000
8469 35000.00000000
8471 35000.00000000
8516 12000.00000000
8518 15000.00000000
8537 29000.00000000
8539 18000.00000000
8585 21000.00000000
8587 29000.00000000
8593 29000.00000000
8594 29000.00000000
8595 24000.00000000
8612 29000.00000000
8615 23000.00000000
8724 29000.00000000
8729 15000.00000000
8730 29000.00000000
8755 21000.00000000
8797 25000.00000000
8798 15000.00000000
8810 29000.00000000
8811 18000.00000000
8814 23000.00000000
8828 29000.00000000
8829 23000.00000000
8831 18000.00000000
10025 25000.00000000
10044 19000.00000000
10046 25000.00000000
11580 22000.00000000
11582 22000.00000000
11584 22000.00000000
11586 22000.00000000
11588 22000.00000000
11589 22000.00000000
11591 22000.00000000
11595 22000.00000000
11596 22000.00000000
11597 22000.00000000
11603 22000.00000000
11604 22000.00000000
11606 22000.00000000
11608 22000.00000000
11609 22000.00000000
11611 22000.00000000