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 (
    7351, 
    7347, 
    7402, 
    7357, 
    7295, 
    7293, 
    7353, 
    7286, 
    7378, 
    7383, 
    7329, 
    7324, 
    7310, 
    7300, 
    7360, 
    7352, 
    7390, 
    7355, 
    7289, 
    7637, 
    7322, 
    7399, 
    7325, 
    7327, 
    7294, 
    7376, 
    7284, 
    7366, 
    7400, 
    7339, 
    7367, 
    7321, 
    7398, 
    7362, 
    7381, 
    7285, 
    7375, 
    7404, 
    7391, 
    7369, 
    7371, 
    7298, 
    7349, 
    7401, 
    7345, 
    7308, 
    7317, 
    7384, 
    7307, 
    7297, 
    7389, 
    7361, 
    7312, 
    7341, 
    7385, 
    7301, 
    7320, 
    7342, 
    7387, 
    7377, 
    7305, 
    7335, 
    7356, 
    7414, 
    7374, 
    7463, 
    7382, 
    7309, 
    7332, 
    7443, 
    7393, 
    7397, 
    7350, 
    7303, 
    7372, 
    7388, 
    7624, 
    7598, 
    7441, 
    7653, 
    7336, 
    7330, 
    7470, 
    7512, 
    7442, 
    7407, 
    7487, 
    7482, 
    7368, 
    7316, 
    7318, 
    7408, 
    7490, 
    7405, 
    7358, 
    7413, 
    7344, 
    7432, 
    7423, 
    7299, 
    7396, 
    7343, 
    7333, 
    7416, 
    7370, 
    7525, 
    7334, 
    7380, 
    7450, 
    7306, 
    7292, 
    7439, 
    7337, 
    7323, 
    7508, 
    7326, 
    7363, 
    7444, 
    7418, 
    7451, 
    7526, 
    7455, 
    7340, 
    7498, 
    7395, 
    7489, 
    7424, 
    7538, 
    7445, 
    7519, 
    7465, 
    7534, 
    7469, 
    7514, 
    7436, 
    7475, 
    7503, 
    7518, 
    7379, 
    7406, 
    7515, 
    7338, 
    7464, 
    7471, 
    7426, 
    7433, 
    57894, 
    27754, 
    7403, 
    7447, 
    7581, 
    7348, 
    7457, 
    7570, 
    7373, 
    7532, 
    7520, 
    7328, 
    7430, 
    7507, 
    7548, 
    7431, 
    7425, 
    7630, 
    7446, 
    7427, 
    7497, 
    7591, 
    7419, 
    7494, 
    7501, 
    7651, 
    7495, 
    7364, 
    7453, 
    7354, 
    7410, 
    7505, 
    7448, 
    7540, 
    7554, 
    7516, 
    7467, 
    7319, 
    7504, 
    7510, 
    7524, 
    7579, 
    7331, 
    7304, 
    7359, 
    7477, 
    7473, 
    7484, 
    7549, 
    7648, 
    7597, 
    7513, 
    7492, 
    7428, 
    7522, 
    7311, 
    7434, 
    7523, 
    7608, 
    7485, 
    7449, 
    7506, 
    7542, 
    7417, 
    7627, 
    7527, 
    7537, 
    7511, 
    7456, 
    7458, 
    7493, 
    7544, 
    7562, 
    7566, 
    7461, 
    7476, 
    7496, 
    7571, 
    7420, 
    7472, 
    7435, 
    7486, 
    7543, 
    7558, 
    7575, 
    7587, 
    7631, 
    7645, 
    7452, 
    7491, 
    7553, 
    7480, 
    7574, 
    70256, 
    7605, 
    70303, 
    70305, 
    7578, 
    70306, 
    71347
  ) 
  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.00214

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "172.21"
    },
    "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": 246,
        "rows_produced_per_join": 49,
        "filtered": "20.00",
        "index_condition": "(`cscartdb`.`cscart_product_prices`.`product_id` in (7351,7347,7402,7357,7295,7293,7353,7286,7378,7383,7329,7324,7310,7300,7360,7352,7390,7355,7289,7637,7322,7399,7325,7327,7294,7376,7284,7366,7400,7339,7367,7321,7398,7362,7381,7285,7375,7404,7391,7369,7371,7298,7349,7401,7345,7308,7317,7384,7307,7297,7389,7361,7312,7341,7385,7301,7320,7342,7387,7377,7305,7335,7356,7414,7374,7463,7382,7309,7332,7443,7393,7397,7350,7303,7372,7388,7624,7598,7441,7653,7336,7330,7470,7512,7442,7407,7487,7482,7368,7316,7318,7408,7490,7405,7358,7413,7344,7432,7423,7299,7396,7343,7333,7416,7370,7525,7334,7380,7450,7306,7292,7439,7337,7323,7508,7326,7363,7444,7418,7451,7526,7455,7340,7498,7395,7489,7424,7538,7445,7519,7465,7534,7469,7514,7436,7475,7503,7518,7379,7406,7515,7338,7464,7471,7426,7433,57894,27754,7403,7447,7581,7348,7457,7570,7373,7532,7520,7328,7430,7507,7548,7431,7425,7630,7446,7427,7497,7591,7419,7494,7501,7651,7495,7364,7453,7354,7410,7505,7448,7540,7554,7516,7467,7319,7504,7510,7524,7579,7331,7304,7359,7477,7473,7484,7549,7648,7597,7513,7492,7428,7522,7311,7434,7523,7608,7485,7449,7506,7542,7417,7627,7527,7537,7511,7456,7458,7493,7544,7562,7566,7461,7476,7496,7571,7420,7472,7435,7486,7543,7558,7575,7587,7631,7645,7452,7491,7553,7480,7574,70256,7605,70303,70305,7578,70306,71347))",
        "cost_info": {
          "read_cost": "167.29",
          "eval_cost": "4.92",
          "prefix_cost": "172.21",
          "data_read_per_join": "1K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`cscartdb`.`cscart_product_prices`.`lower_limit` = 1) and (`cscartdb`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
7284 17.50000000
7285 18.00000000
7286 14.50000000
7289 25.00000000
7292 14.50000000
7293 23.50000000
7294 14.50000000
7295 49.00000000
7297 34.50000000
7298 14.50000000
7299 15.00000000
7300 35.00000000
7301 12.50000000
7303 18.00000000
7304 16.00000000
7305 33.50000000
7306 16.50000000
7307 30.00000000
7308 20.00000000
7309 25.00000000
7310 12.50000000
7311 17.50000000
7312 17.50000000
7316 16.75000000
7317 11.50000000
7318 12.50000000
7319 13.00000000
7320 32.00000000
7321 16.50000000
7322 25.00000000
7323 20.00000000
7324 16.00000000
7325 47.00000000
7326 27.00000000
7327 14.00000000
7328 29.00000000
7329 22.00000000
7330 13.00000000
7331 38.00000000
7332 16.00000000
7333 16.00000000
7334 14.00000000
7335 13.50000000
7336 12.00000000
7337 20.00000000
7338 28.00000000
7339 15.50000000
7340 33.00000000
7341 22.00000000
7342 16.00000000
7343 17.00000000
7344 27.00000000
7345 18.00000000
7347 8.75000000
7348 15.25000000
7349 14.00000000
7350 16.00000000
7351 15.50000000
7352 28.00000000
7353 19.00000000
7354 17.00000000
7355 33.00000000
7356 75.00000000
7357 17.00000000
7358 12.50000000
7359 8.50000000
7360 17.50000000
7361 12.50000000
7362 14.50000000
7363 15.00000000
7364 16.50000000
7366 16.50000000
7367 22.00000000
7368 26.00000000
7369 16.50000000
7370 14.50000000
7371 14.00000000
7372 20.00000000
7373 45.00000000
7374 22.00000000
7375 14.50000000
7376 24.00000000
7377 28.00000000
7378 17.50000000
7379 13.00000000
7380 15.00000000
7381 15.25000000
7382 30.00000000
7383 28.00000000
7384 33.00000000
7385 27.00000000
7387 14.00000000
7388 35.00000000
7389 11.50000000
7390 16.00000000
7391 14.00000000
7393 20.00000000
7395 26.00000000
7396 18.00000000
7397 14.75000000
7398 16.50000000
7399 15.50000000
7400 15.00000000
7401 25.00000000
7402 23.00000000
7403 15.00000000
7404 19.50000000
7405 14.50000000
7406 25.00000000
7407 11.00000000
7408 80.00000000
7410 17.00000000
7413 25.00000000
7414 78.00000000
7416 17.00000000
7417 18.50000000
7418 16.00000000
7419 24.00000000
7420 18.00000000
7423 35.00000000
7424 25.00000000
7425 11.00000000
7426 30.00000000
7427 26.00000000
7428 16.50000000
7430 25.00000000
7431 17.50000000
7432 32.00000000
7433 50.00000000
7434 45.00000000
7435 30.00000000
7436 35.00000000
7439 19.00000000
7441 16.50000000
7442 16.50000000
7443 15.00000000
7444 17.00000000
7445 22.00000000
7446 28.00000000
7447 15.00000000
7448 25.00000000
7449 15.50000000
7450 14.50000000
7451 65.00000000
7452 16.50000000
7453 30.00000000
7455 18.50000000
7456 42.00000000
7457 32.00000000
7458 16.75000000
7461 20.00000000
7463 13.00000000
7464 35.00000000
7465 32.00000000
7467 32.00000000
7469 17.00000000
7470 27.00000000
7471 16.50000000
7472 15.00000000
7473 16.00000000
7475 27.00000000
7476 16.00000000
7477 16.50000000
7480 20.00000000
7482 12.50000000
7484 30.00000000
7485 16.00000000
7486 13.00000000
7487 17.00000000
7489 13.50000000
7490 14.50000000
7491 32.00000000
7492 12.50000000
7493 13.00000000
7494 24.00000000
7495 28.00000000
7496 45.00000000
7497 28.00000000
7498 35.00000000
7501 20.00000000
7503 58.50000000
7504 18.50000000
7505 23.00000000
7506 43.50000000
7507 12.75000000
7508 12.50000000
7510 16.00000000
7511 27.50000000
7512 17.00000000
7513 12.50000000
7514 16.50000000
7515 20.00000000
7516 15.00000000
7518 11.00000000
7519 13.00000000
7520 13.00000000
7522 34.00000000
7523 15.50000000
7524 12.00000000
7525 12.00000000
7526 12.00000000
7527 30.00000000
7532 14.00000000
7534 19.50000000
7537 32.00000000
7538 14.50000000
7540 15.50000000
7542 15.50000000
7543 15.50000000
7544 14.50000000
7548 22.00000000
7549 16.75000000
7553 30.00000000
7554 18.00000000
7558 28.00000000
7562 8.50000000
7566 12.00000000
7570 17.50000000
7571 17.00000000
7574 22.00000000
7575 35.00000000
7578 18.00000000
7579 18.00000000
7581 15.00000000
7587 16.00000000
7591 16.50000000
7597 16.50000000
7598 15.00000000
7605 23.00000000
7608 16.50000000
7624 15.00000000
7627 16.50000000
7630 13.50000000
7631 16.00000000
7637 25.00000000
7645 25.00000000
7648 19.00000000
7651 16.50000000
7653 60.00000000
27754 18.00000000
57894 18.00000000
70256 16.00000000
70303 18.00000000
70305 15.00000000
70306 14.00000000
71347 28.00000000