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 (
    51567, 
    51565, 
    51564, 
    139, 
    129, 
    51566, 
    51301, 
    51, 
    50, 
    51497, 
    51498, 
    53, 
    22713, 
    21, 
    22714, 
    22712, 
    46, 
    22715, 
    22716, 
    51496, 
    21567, 
    91, 
    50989, 
    47, 
    105, 
    48, 
    49, 
    92, 
    137, 
    88, 
    126, 
    31, 
    51358, 
    115, 
    37, 
    11, 
    140, 
    41, 
    30, 
    90, 
    38, 
    124, 
    81, 
    56, 
    135, 
    82, 
    116, 
    117, 
    125, 
    106, 
    39, 
    193, 
    65, 
    40, 
    22710, 
    22711, 
    17, 
    57, 
    122, 
    58, 
    5, 
    22, 
    18, 
    27, 
    121, 
    119, 
    141, 
    23, 
    4, 
    142, 
    120, 
    62, 
    108, 
    107, 
    94, 
    8, 
    86, 
    54, 
    10, 
    93, 
    12, 
    32, 
    7, 
    130, 
    35, 
    16, 
    33, 
    110, 
    34, 
    89, 
    147, 
    114, 
    36, 
    128, 
    20, 
    52, 
    84, 
    109, 
    118, 
    19, 
    131, 
    146, 
    13, 
    61, 
    95, 
    15, 
    9, 
    85, 
    59, 
    111, 
    144, 
    60, 
    123, 
    145, 
    113, 
    6, 
    14, 
    63, 
    136, 
    50984, 
    127, 
    134, 
    42, 
    50985, 
    50986, 
    50988, 
    50993, 
    50992
  ) 
  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.00220

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "101.88"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "ref",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "lower_limit",
        "used_key_parts": [
          "lower_limit"
        ],
        "key_length": "3",
        "ref": [
          "const"
        ],
        "rows_examined_per_scan": 970,
        "rows_produced_per_join": 37,
        "filtered": "3.91",
        "cost_info": {
          "read_cost": "4.88",
          "eval_cost": "3.79",
          "prefix_cost": "101.88",
          "data_read_per_join": "910"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`test_uchur_k`.`cscart_product_prices`.`product_id` in (51567,51565,51564,139,129,51566,51301,51,50,51497,51498,53,22713,21,22714,22712,46,22715,22716,51496,21567,91,50989,47,105,48,49,92,137,88,126,31,51358,115,37,11,140,41,30,90,38,124,81,56,135,82,116,117,125,106,39,193,65,40,22710,22711,17,57,122,58,5,22,18,27,121,119,141,23,4,142,120,62,108,107,94,8,86,54,10,93,12,32,7,130,35,16,33,110,34,89,147,114,36,128,20,52,84,109,118,19,131,146,13,61,95,15,9,85,59,111,144,60,123,145,113,6,14,63,136,50984,127,134,42,50985,50986,50988,50993,50992)) and (`test_uchur_k`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
7 27300.00000000
8 27300.00000000
9 9700.00000000
10 8400.00000000
11 8400.00000000
12 27300.00000000
13 12600.00000000
16 16100.00000000
19 105000.00000000
21 224000.00000000
30 93800.00000000
31 93800.00000000
32 25200.00000000
33 25200.00000000
34 25200.00000000
35 23800.00000000
36 23800.00000000
37 95200.00000000
38 95200.00000000
39 95200.00000000
40 109900.00000000
41 109900.00000000
42 33300.00000000
46 56000.00000000
47 179200.00000000
48 164500.00000000
49 164500.00000000
50 483000.00000000
51 483000.00000000
52 112000.00000000
53 224000.00000000
54 91000.00000000
58 10500.00000000
59 11200.00000000
65 28700.00000000
89 28000.00000000
90 146300.00000000
91 146300.00000000
92 164500.00000000
93 90300.00000000
94 32200.00000000
95 10500.00000000
105 40600.00000000
106 40600.00000000
107 13300.00000000
109 27300.00000000
113 14000.00000000
116 15400.00000000
117 15400.00000000
120 14000.00000000
121 15400.00000000
123 14000.00000000
125 27300.00000000
126 11200.00000000
128 102200.00000000
129 45500.00000000
130 84000.00000000
131 84000.00000000
134 37100.00000000
135 19600.00000000
136 16800.00000000
137 25200.00000000
139 49000.00000000
140 28000.00000000
141 7700.00000000
142 10500.00000000
144 27300.00000000
145 27300.00000000
146 27300.00000000
147 27300.00000000
4 15400.00000000
5 15400.00000000
6 15400.00000000
14 12600.00000000
15 16100.00000000
17 16100.00000000
18 16100.00000000
20 105000.00000000
22 14000.00000000
23 14000.00000000
27 14000.00000000
56 14000.00000000
57 10500.00000000
60 11200.00000000
61 12600.00000000
62 11200.00000000
63 6300.00000000
81 14000.00000000
82 14000.00000000
84 3500.00000000
85 7000.00000000
86 19600.00000000
88 28700.00000000
193 146300.00000000
108 13300.00000000
110 27300.00000000
111 27300.00000000
114 15400.00000000
115 15400.00000000
118 14000.00000000
119 14000.00000000
122 15400.00000000
124 27300.00000000
127 35000.00000000
51497 14800.00000000
51496 14800.00000000
21567 9100.00000000
22710 9100.00000000
22711 18200.00000000
22712 15400.00000000
22713 15400.00000000
22714 15400.00000000
22715 15400.00000000
22716 15400.00000000
51301 39200.00000000
51358 56000.00000000
51498 14800.00000000
51567 230900.00000000
51566 168000.00000000
51565 195900.00000000
51564 147000.00000000
50993 22330.00000000
50988 40530.00000000
50989 8400.00000000
50992 46130.00000000
50984 29400.00000000
50985 27930.00000000
50986 32130.00000000