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 (
    4, 
    1, 
    5, 
    7, 
    8, 
    9, 
    10, 
    11, 
    12, 
    13, 
    14, 
    15, 
    16, 
    17, 
    18, 
    19, 
    20, 
    21, 
    22, 
    23, 
    24, 
    25, 
    26, 
    27, 
    28, 
    29, 
    30, 
    31, 
    32, 
    33, 
    34, 
    35, 
    36, 
    37, 
    38, 
    39, 
    40, 
    41, 
    42, 
    43, 
    44, 
    45, 
    46, 
    47, 
    48, 
    49, 
    50, 
    51, 
    52, 
    53, 
    54, 
    55, 
    56, 
    57, 
    58, 
    59, 
    60, 
    62, 
    63, 
    64, 
    65, 
    67, 
    68, 
    69, 
    70, 
    71, 
    72, 
    73, 
    74, 
    75, 
    76, 
    78, 
    79, 
    80, 
    81, 
    82, 
    83, 
    84, 
    85, 
    86, 
    87, 
    88, 
    89, 
    90, 
    91, 
    92, 
    93, 
    94, 
    95, 
    96, 
    97, 
    100, 
    101, 
    102, 
    103, 
    104, 
    105, 
    106, 
    107, 
    108, 
    109, 
    110, 
    111, 
    112, 
    113, 
    114, 
    115, 
    116, 
    117, 
    118, 
    119, 
    120, 
    121, 
    122, 
    123, 
    124, 
    125, 
    126, 
    127, 
    128, 
    129, 
    130, 
    131, 
    132, 
    133, 
    134, 
    135, 
    136
  ) 
  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.00133

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "307.98"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "254.00"
      },
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "ALL",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "rows_examined_per_scan": 254,
        "rows_produced_per_join": 254,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "3.18",
          "eval_cost": "50.80",
          "prefix_cost": "53.98",
          "data_read_per_join": "5K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`pankajecarter_systemtwo`.`cscart_product_prices`.`lower_limit` = 1) and (`pankajecarter_systemtwo`.`cscart_product_prices`.`product_id` in (4,1,5,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,62,63,64,65,67,68,69,70,71,72,73,74,75,76,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136)) and (`pankajecarter_systemtwo`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
1 5399.99000000
4 699.99000000
5 899.99000000
7 55.00000000
8 849.99000000
9 999.99000000
10 1199.99000000
11 30.00000000
12 30.00000000
13 11.96000000
14 499.99000000
15 150.00000000
16 349.99000000
17 11.16000000
18 299.99000000
19 79.99000000
20 19.95000000
21 29.99000000
22 799.99000000
23 599.99000000
24 449.99000000
25 599.99000000
26 34.99000000
27 55.00000000
28 100.00000000
29 199.95000000
30 349.95000000
31 32.00000000
32 299.99000000
33 169.99000000
34 25.00000000
35 31.99000000
36 51.00000000
37 159.95000000
38 16.97000000
39 419.00000000
40 229.00000000
41 35.00000000
42 79.00000000
43 369.00000000
44 25.00000000
45 74.00000000
46 21.00000000
47 29.99000000
48 180.00000000
49 120.00000000
50 220.00000000
51 180.00000000
52 139.99000000
53 38.99000000
54 269.00000000
55 359.00000000
56 220.00000000
57 309.00000000
58 779.00000000
59 599.00000000
60 1499.00000000
62 209.95000000
63 189.95000000
64 189.95000000
65 99.95000000
67 339.99000000
68 799.99000000
69 529.99000000
70 499.99000000
71 529.99000000
72 599.99000000
73 589.99000000
74 524.99000000
75 489.99000000
76 439.99000000
78 100.00000000
79 96.00000000
80 55.00000000
81 49.50000000
82 19.99000000
83 19.99000000
84 19.99000000
85 19.99000000
86 359.00000000
87 19.99000000
88 39.99000000
89 19.99000000
90 19.99000000
91 10700.00000000
92 3225.00000000
93 19.99000000
94 59.99000000
95 19.99000000
96 99.99000000
97 14.99000000
100 22.70000000
101 188.88000000
102 295.00000000
103 23.99000000
104 29.95000000
105 169.99000000
106 179.99000000
107 465.00000000
108 12.99000000
109 140.00000000
110 15.99000000
111 6.99000000
112 8.99000000
113 449.99000000
114 14.99000000
115 4595.00000000
116 6.99000000
117 729.99000000
118 30.99000000
119 17.99000000
120 199.99000000
121 17.99000000
122 5695.00000000
123 17.99000000
124 4595.00000000
125 149.99000000
126 129.99000000
127 4779.00000000
128 17.99000000
129 1799.00000000
130 49.95000000
131 1249.00000000
132 269.99000000
133 229.99000000
134 89.99000000
135 0.00000000
136 0.00000000