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 (
    278, 
    280, 
    282, 
    247, 
    248, 
    241, 
    244, 
    245, 
    246, 
    240, 
    6, 
    66, 
    166, 
    214, 
    219, 
    221, 
    223, 
    192, 
    194, 
    195, 
    196, 
    197, 
    198, 
    199, 
    200, 
    201, 
    202, 
    203, 
    204, 
    205, 
    206, 
    207, 
    208, 
    209, 
    210, 
    211, 
    212, 
    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, 
    185, 
    186, 
    187, 
    188, 
    189, 
    190, 
    191, 
    1, 
    5, 
    7, 
    8, 
    9, 
    10, 
    11, 
    12, 
    13, 
    14, 
    15, 
    16, 
    17, 
    18, 
    19, 
    20, 
    21, 
    22, 
    23, 
    24
  ) 
  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.00438

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "cscart_product_prices.product_id",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "cscart_product_prices",
              "access_type": "ALL",
              "possible_keys": [
                "usergroup",
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "rows": 253,
              "filtered": 84.8130722,
              "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.product_id in (278,280,282,247,248,241,244,245,246,240,6,66,166,214,219,221,223,192,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,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,185,186,187,188,189,190,191,1,5,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24) and cscart_product_prices.usergroup_id in (0,1)"
            }
          }
        ]
      }
    }
  }
}

Result

product_id price
1 5399.99000000
5 899.99000000
6 329.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
66 389.95000000
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
166 749.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
185 139.99000000
186 299.99000000
187 299.99000000
188 10.00000000
189 1.00000000
190 899.95000000
191 11.98000000
192 15.00000000
194 10.60000000
195 29.99000000
196 17.00000000
197 14.98000000
198 17.99000000
199 29.98000000
200 26.92000000
201 12.67000000
202 34.68000000
203 34.68000000
204 14.99000000
205 149.99000000
206 179.99000000
207 42.00000000
208 82.94000000
209 109.99000000
210 89.99000000
211 299.99000000
212 129.95000000
213 295.00000000
214 972.00000000
215 1095.00000000
217 610.99000000
218 459.99000000
219 529.99000000
220 1099.99000000
221 2049.00000000
222 529.99000000
223 499.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
240 499.00000000
241 499.00000000
242 249.00000000
243 249.00000000
244 729.99000000
245 699.00000000
246 399.99000000
247 329.49000000
248 372.27000000
278 30.00000000
280 36.00000000
282 27.99000000