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 (
    286, 
    278, 
    280, 
    282, 
    247, 
    255, 
    256, 
    257, 
    249, 
    250, 
    251, 
    252, 
    253, 
    254, 
    248, 
    245, 
    246, 
    66, 
    166, 
    192, 
    194, 
    195, 
    196, 
    197, 
    198, 
    199, 
    200, 
    201, 
    202, 
    203, 
    204, 
    230, 
    231, 
    232, 
    233, 
    234, 
    235, 
    236, 
    237, 
    238, 
    239, 
    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, 
    127, 
    129, 
    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, 
    160, 
    161, 
    163, 
    164, 
    165, 
    167, 
    168, 
    169, 
    170, 
    172, 
    173, 
    175
  ) 
  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.00710

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": 264,
              "filtered": 55.23989868,
              "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.product_id in (286,278,280,282,247,255,256,257,249,250,251,252,253,254,248,245,246,66,166,192,194,195,196,197,198,199,200,201,202,203,204,230,231,232,233,234,235,236,237,238,239,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,127,129,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,160,161,163,164,165,167,168,169,170,172,173,175) and cscart_product_prices.usergroup_id in (0,1)"
            }
          }
        ]
      }
    }
  }
}

Result

product_id price
66 15598.00000000
78 4000.00000000
79 3840.00000000
80 2200.00000000
81 1980.00000000
82 799.60000000
83 799.60000000
84 799.60000000
85 799.60000000
86 14360.00000000
87 799.60000000
88 1599.60000000
89 799.60000000
90 799.60000000
91 428000.00000000
92 129000.00000000
93 799.60000000
94 2399.60000000
95 799.60000000
96 3999.60000000
97 599.60000000
100 908.00000000
101 3610.80000000
102 11800.00000000
103 959.60000000
104 1198.00000000
105 6799.60000000
106 7199.60000000
107 18600.00000000
108 519.60000000
109 5600.00000000
110 639.60000000
111 279.60000000
112 359.60000000
113 17999.60000000
114 599.60000000
115 183800.00000000
116 279.60000000
117 29199.60000000
118 1239.60000000
119 719.60000000
120 7999.60000000
121 719.60000000
122 227800.00000000
123 719.60000000
124 80825.00000000
125 5999.60000000
127 191160.00000000
129 71960.00000000
131 49960.00000000
132 10799.60000000
133 9199.60000000
134 3599.60000000
135 0.00000000
136 0.00000000
137 0.00000000
138 0.00000000
139 0.00000000
140 3998.00000000
141 3998.00000000
142 15998.00000000
143 3198.00000000
144 3998.00000000
145 3199.60000000
146 1799.60000000
147 1799.60000000
148 5200.00000000
149 2159.60000000
150 4800.00000000
151 20000.00000000
152 28000.00000000
153 1999.60000000
154 15999.60000000
155 3199.60000000
156 11960.00000000
157 19960.00000000
160 8000.00000000
161 11199.60000000
163 35999.60000000
164 3749.65000000
165 9998.25000000
166 12248.25000000
167 19248.25000000
168 40.00000000
169 29998.00000000
170 5839.60000000
172 11999.60000000
173 13999.60000000
175 7199.60000000
192 600.00000000
194 424.00000000
195 1199.60000000
196 680.00000000
197 599.20000000
198 719.60000000
199 1199.20000000
200 1076.80000000
201 506.80000000
202 1387.20000000
203 1387.20000000
204 599.60000000
230 1675.00000000
231 1275.00000000
232 1275.00000000
233 1919.60000000
234 2399.60000000
235 3199.60000000
236 5499.65000000
237 11999.60000000
238 19999.60000000
239 20399.60000000
245 27960.00000000
246 15999.60000000
247 13179.60000000
248 10000.00000000
249 13990.00000000
250 20990.00000000
251 2890.00000000
252 2990.00000000
253 1200.00000000
254 1990.00000000
255 2770.00000000
256 1100.00000000
257 3200.00000000
278 75.00000000
280 50.00000000
282 75.00000000
286 100.00000000