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 (
    213, 
    215, 
    217, 
    218, 
    220, 
    222, 
    224, 
    225, 
    226, 
    227, 
    228, 
    229, 
    230, 
    231, 
    232, 
    233, 
    234, 
    235, 
    236, 
    237, 
    238, 
    239, 
    242, 
    243, 
    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, 
    137, 
    138, 
    139, 
    140, 
    141, 
    142, 
    143, 
    144, 
    145, 
    146, 
    147, 
    148, 
    149, 
    150, 
    151, 
    152, 
    153, 
    154, 
    155, 
    156, 
    157, 
    158, 
    159, 
    160, 
    161, 
    162, 
    163, 
    164, 
    165, 
    167, 
    168, 
    169, 
    170, 
    171, 
    172, 
    173, 
    174, 
    175, 
    176, 
    177, 
    178, 
    179, 
    180, 
    181, 
    182, 
    183, 
    184
  ) 
  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.00142

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "336.45"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "256.00"
      },
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "ALL",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "rows_examined_per_scan": 383,
        "rows_produced_per_join": 256,
        "filtered": "66.84",
        "cost_info": {
          "read_cost": "29.26",
          "eval_cost": "51.20",
          "prefix_cost": "80.46",
          "data_read_per_join": "6K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`pankajecarter_systemfour`.`cscart_product_prices`.`lower_limit` = 1) and (`pankajecarter_systemfour`.`cscart_product_prices`.`product_id` in (213,215,217,218,220,222,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,242,243,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,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184)) and (`pankajecarter_systemfour`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
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
137 0.00000000
138 0.00000000
139 0.00000000
140 99.95000000
141 99.95000000
142 399.95000000
143 79.95000000
144 99.95000000
145 79.99000000
146 44.99000000
147 44.99000000
148 130.00000000
149 53.99000000
150 120.00000000
151 500.00000000
152 700.00000000
153 49.99000000
154 399.99000000
155 79.99000000
156 299.00000000
157 499.00000000
158 4750.00000000
159 11375.00000000
160 200.00000000
161 279.99000000
162 32750.00000000
163 899.99000000
164 249.99000000
165 599.95000000
167 549.95000000
168 1.00000000
169 749.95000000
170 145.99000000
171 499.00000000
172 299.99000000
173 349.99000000
174 100.75000000
175 179.99000000
176 648.95000000
177 400.00000000
178 6.83000000
179 299.97000000
180 199.99000000
181 899.99000000
182 6.80000000
183 249.99000000
184 299.99000000
213 295.00000000
215 1095.00000000
217 610.99000000
218 459.99000000
220 1099.99000000
222 529.99000000
224 479.99000000
225 199.99000000
226 269.99000000
227 699.00000000
228 349.99000000
229 299.99000000
230 125.00000000
231 99.00000000
232 79.95000000
233 47.99000000
234 59.99000000
235 79.99000000
236 299.99000000
237 299.99000000
238 499.99000000
239 509.99000000
242 249.00000000
243 249.00000000