Sandbox

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 (
    35698, 
    35877, 
    35344, 
    35350, 
    35218, 
    35530, 
    35279, 
    35267, 
    35284, 
    35310, 
    35418, 
    35215, 
    35306, 
    35395, 
    35328, 
    35772, 
    35341, 
    35562, 
    35220, 
    35333, 
    35237, 
    35438, 
    35948, 
    35225, 
    35226, 
    35296, 
    35420, 
    35261, 
    35278, 
    35415, 
    35730, 
    35325, 
    35590, 
    35272, 
    35235, 
    35316, 
    35464, 
    35242, 
    35227, 
    35277, 
    35217, 
    35828, 
    35320, 
    35388, 
    35294, 
    55606, 
    35449, 
    35458, 
    35390, 
    35417, 
    35462, 
    35382, 
    35460, 
    35394, 
    35459, 
    35266, 
    35493, 
    35934, 
    35240, 
    35257, 
    35371, 
    35612, 
    35450, 
    35224, 
    35270, 
    35403, 
    35619, 
    35256, 
    35263, 
    35439, 
    35243, 
    35283, 
    35939, 
    35423, 
    35689, 
    35741, 
    35258, 
    35351, 
    35354, 
    35419, 
    35440, 
    35330, 
    35355, 
    35372, 
    35611, 
    35838, 
    35964, 
    35694, 
    35319, 
    35282, 
    35321, 
    35840, 
    35891, 
    35265, 
    35298, 
    35301, 
    35255, 
    35368, 
    35507, 
    35516, 
    35655, 
    35697, 
    35796, 
    35830, 
    35308, 
    35314, 
    35489, 
    35605, 
    35613, 
    35696, 
    35832, 
    35568, 
    35252, 
    35305, 
    35401, 
    35856, 
    35299, 
    35428, 
    35435, 
    35456, 
    35616, 
    35443, 
    35444, 
    35234, 
    35287, 
    35307, 
    35309, 
    35312, 
    35352, 
    35357, 
    35426, 
    35541, 
    35239, 
    35288, 
    35246, 
    35281, 
    35290, 
    35302, 
    35329, 
    35381, 
    35387, 
    35404, 
    35536, 
    35614, 
    35685, 
    35782, 
    35800, 
    35811, 
    35841, 
    35842, 
    35875, 
    55607, 
    35802, 
    35317, 
    35359, 
    35682, 
    35846, 
    35902, 
    35236, 
    35241, 
    35260, 
    35291, 
    35304, 
    35322, 
    35340, 
    35345, 
    35379, 
    35411, 
    35416, 
    35486, 
    35583, 
    35610, 
    35702, 
    35813, 
    35816, 
    35936, 
    35262, 
    35274, 
    35323, 
    35343, 
    35408, 
    35249, 
    35268, 
    35269, 
    35286, 
    35297, 
    35300, 
    35313, 
    35334, 
    35414, 
    35429, 
    35434, 
    35445, 
    35490, 
    35506, 
    35622, 
    35623, 
    35674, 
    35837, 
    35878, 
    35315, 
    35807, 
    35808, 
    35918, 
    35925, 
    35214, 
    35216, 
    35248, 
    35318, 
    35327, 
    35331, 
    35337, 
    35365, 
    35380, 
    35398, 
    35413, 
    35505, 
    35529, 
    35539, 
    35560, 
    35624, 
    35625, 
    35810, 
    35230, 
    35238, 
    35250, 
    35271, 
    35289, 
    35361, 
    35362, 
    35366, 
    35389, 
    35392, 
    35421, 
    35615, 
    35621, 
    35633, 
    35692, 
    35898, 
    35944, 
    35427, 
    35433, 
    35229, 
    35244, 
    35264, 
    35280, 
    35285, 
    35356, 
    35358, 
    35369, 
    35374, 
    35376, 
    35386, 
    35391, 
    35430, 
    35437, 
    35546, 
    35617, 
    35618, 
    35704, 
    35705, 
    35706, 
    35806, 
    35809, 
    35822, 
    35831, 
    35848, 
    35879, 
    35897, 
    35927, 
    35402, 
    35273, 
    35275, 
    35335, 
    35338, 
    35373, 
    35384, 
    35436, 
    35442, 
    35471, 
    35472, 
    35571, 
    35681, 
    35690, 
    35799, 
    35812, 
    35815, 
    35869, 
    35907, 
    35952, 
    35966, 
    35231, 
    35233, 
    35245, 
    35251, 
    35253, 
    35259, 
    35276, 
    35303, 
    35311, 
    35364, 
    35396, 
    35407, 
    35513, 
    35603, 
    35638, 
    35684, 
    35693, 
    35845, 
    35862, 
    35904, 
    35247, 
    35336, 
    35360, 
    35375, 
    35377, 
    35393, 
    35397, 
    35405, 
    35406, 
    35441, 
    35452, 
    35485, 
    35509, 
    35558, 
    35628, 
    35703, 
    35783, 
    35943, 
    35363, 
    35367, 
    35446, 
    35491, 
    35495, 
    35510, 
    35540, 
    35602, 
    35653, 
    35385, 
    35399, 
    35412, 
    35424, 
    35432, 
    35447, 
    35453, 
    35457, 
    35517, 
    35915, 
    35451, 
    35473, 
    35477, 
    35488, 
    35535, 
    35548, 
    35580, 
    35597, 
    35636, 
    35448, 
    35492, 
    35526, 
    35554, 
    35570, 
    35725, 
    35497, 
    35502, 
    35561, 
    35604, 
    35659, 
    35673, 
    35758, 
    35383, 
    35409, 
    35463, 
    35515, 
    35559, 
    35637, 
    35639, 
    35652, 
    35656, 
    35666, 
    35675, 
    35691, 
    35701, 
    35726, 
    35751, 
    35461, 
    35596, 
    35630, 
    35645, 
    35671, 
    35677, 
    35678, 
    35688, 
    35707, 
    35710, 
    35724, 
    35727, 
    35733, 
    35735, 
    35756, 
    35781, 
    35672, 
    35683, 
    35699, 
    35700, 
    35708, 
    35715, 
    35740, 
    35743, 
    35744, 
    35748, 
    35759, 
    35937, 
    35524, 
    35537, 
    35728, 
    35747, 
    35755, 
    35679, 
    35729, 
    35466, 
    35482, 
    35454, 
    35455, 
    35467, 
    35468, 
    35469, 
    35474, 
    35475, 
    35476, 
    35483, 
    35484, 
    35496, 
    35500, 
    35508, 
    35514, 
    35518, 
    35521, 
    35523, 
    35528, 
    35532, 
    35533, 
    35538, 
    35555
  ) 
  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.00357

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "310.81"
    },
    "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": 444,
        "rows_produced_per_join": 88,
        "filtered": "20.00",
        "index_condition": "(`cscartdb`.`cscart_product_prices`.`product_id` in (35698,35877,35344,35350,35218,35530,35279,35267,35284,35310,35418,35215,35306,35395,35328,35772,35341,35562,35220,35333,35237,35438,35948,35225,35226,35296,35420,35261,35278,35415,35730,35325,35590,35272,35235,35316,35464,35242,35227,35277,35217,35828,35320,35388,35294,55606,35449,35458,35390,35417,35462,35382,35460,35394,35459,35266,35493,35934,35240,35257,35371,35612,35450,35224,35270,35403,35619,35256,35263,35439,35243,35283,35939,35423,35689,35741,35258,35351,35354,35419,35440,35330,35355,35372,35611,35838,35964,35694,35319,35282,35321,35840,35891,35265,35298,35301,35255,35368,35507,35516,35655,35697,35796,35830,35308,35314,35489,35605,35613,35696,35832,35568,35252,35305,35401,35856,35299,35428,35435,35456,35616,35443,35444,35234,35287,35307,35309,35312,35352,35357,35426,35541,35239,35288,35246,35281,35290,35302,35329,35381,35387,35404,35536,35614,35685,35782,35800,35811,35841,35842,35875,55607,35802,35317,35359,35682,35846,35902,35236,35241,35260,35291,35304,35322,35340,35345,35379,35411,35416,35486,35583,35610,35702,35813,35816,35936,35262,35274,35323,35343,35408,35249,35268,35269,35286,35297,35300,35313,35334,35414,35429,35434,35445,35490,35506,35622,35623,35674,35837,35878,35315,35807,35808,35918,35925,35214,35216,35248,35318,35327,35331,35337,35365,35380,35398,35413,35505,35529,35539,35560,35624,35625,35810,35230,35238,35250,35271,35289,35361,35362,35366,35389,35392,35421,35615,35621,35633,35692,35898,35944,35427,35433,35229,35244,35264,35280,35285,35356,35358,35369,35374,35376,35386,35391,35430,35437,35546,35617,35618,35704,35705,35706,35806,35809,35822,35831,35848,35879,35897,35927,35402,35273,35275,35335,35338,35373,35384,35436,35442,35471,35472,35571,35681,35690,35799,35812,35815,35869,35907,35952,35966,35231,35233,35245,35251,35253,35259,35276,35303,35311,35364,35396,35407,35513,35603,35638,35684,35693,35845,35862,35904,35247,35336,35360,35375,35377,35393,35397,35405,35406,35441,35452,35485,35509,35558,35628,35703,35783,35943,35363,35367,35446,35491,35495,35510,35540,35602,35653,35385,35399,35412,35424,35432,35447,35453,35457,35517,35915,35451,35473,35477,35488,35535,35548,35580,35597,35636,35448,35492,35526,35554,35570,35725,35497,35502,35561,35604,35659,35673,35758,35383,35409,35463,35515,35559,35637,35639,35652,35656,35666,35675,35691,35701,35726,35751,35461,35596,35630,35645,35671,35677,35678,35688,35707,35710,35724,35727,35733,35735,35756,35781,35672,35683,35699,35700,35708,35715,35740,35743,35744,35748,35759,35937,35524,35537,35728,35747,35755,35679,35729,35466,35482,35454,35455,35467,35468,35469,35474,35475,35476,35483,35484,35496,35500,35508,35514,35518,35521,35523,35528,35532,35533,35538,35555))",
        "cost_info": {
          "read_cost": "301.93",
          "eval_cost": "8.88",
          "prefix_cost": "310.81",
          "data_read_per_join": "2K"
        },
        "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
35214	18.95000000
35215	15.95000000
35216	42.95000000
35217	16.95000000
35218	26.95000000
35220	15.95000000
35224	19.95000000
35225	16.75000000
35226	15.50000000
35227	17.95000000
35229	23.95000000
35230	26.95000000
35231	20.95000000
35233	30.95000000
35234	20.95000000
35235	18.95000000
35236	28.50000000
35237	9.95000000
35238	15.50000000
35239	17.95000000
35240	18.95000000
35241	28.95000000
35242	19.95000000
35243	19.95000000
35244	31.95000000
35245	21.95000000
35246	27.95000000
35247	18.95000000
35248	18.95000000
35249	18.95000000
35250	19.95000000
35251	24.95000000
35252	15.95000000
35253	19.95000000
35255	21.95000000
35256	21.95000000
35257	15.95000000
35258	23.95000000
35259	25.95000000
35260	14.95000000
35261	15.95000000
35262	9.95000000
35263	15.95000000
35264	3.95000000
35265	20.95000000
35266	9.95000000
35267	19.95000000
35268	32.95000000
35269	22.95000000
35270	19.95000000
35271	23.95000000
35272	12.95000000
35273	18.95000000
35274	10.95000000
35275	9.95000000
35276	18.95000000
35277	14.95000000
35278	15.95000000
35279	19.95000000
35280	19.95000000
35281	16.95000000
35282	10.95000000
35283	15.95000000
35284	18.95000000
35285	24.95000000
35286	18.95000000
35287	13.95000000
35288	18.95000000
35289	20.95000000
35290	29.95000000
35291	14.95000000
35294	10.95000000
35296	28.95000000
35297	18.95000000
35298	16.95000000
35299	18.95000000
35300	15.95000000
35301	18.95000000
35302	23.95000000
35303	28.95000000
35304	26.95000000
35305	15.95000000
35306	10.95000000
35307	18.95000000
35308	18.95000000
35309	15.95000000
35310	18.95000000
35311	21.95000000
35312	26.95000000
35313	20.95000000
35314	18.95000000
35315	14.95000000
35316	10.95000000
35317	18.95000000
35318	29.95000000
35319	9.95000000
35320	14.95000000
35321	14.95000000
35322	15.95000000
35323	15.95000000
35325	23.95000000
35327	18.95000000
35328	15.95000000
35329	14.95000000
35330	14.95000000
35331	59.95000000
35333	18.95000000
35334	39.95000000
35335	15.95000000
35336	18.95000000
35337	19.95000000
35338	19.95000000
35340	18.95000000
35341	9.95000000
35343	17.95000000
35344	18.95000000
35345	14.95000000
35350	9.95000000
35351	59.95000000
35352	13.95000000
35354	21.95000000
35355	20.95000000
35356	19.95000000
35357	26.95000000
35358	10.95000000
35359	18.95000000
35360	28.95000000
35361	14.95000000
35362	27.95000000
35363	27.95000000
35364	18.95000000
35365	14.95000000
35366	30.95000000
35367	18.95000000
35368	21.95000000
35369	4.95000000
35371	15.95000000
35372	21.95000000
35373	16.95000000
35374	18.95000000
35375	31.95000000
35376	18.95000000
35377	6.95000000
35379	31.95000000
35380	27.95000000
35381	33.95000000
35382	23.95000000
35383	17.95000000
35384	18.95000000
35385	17.95000000
35386	18.95000000
35387	15.50000000
35388	18.95000000
35389	21.95000000
35390	15.95000000
35391	15.95000000
35392	19.95000000
35393	19.95000000
35394	28.95000000
35395	27.95000000
35396	18.95000000
35397	27.95000000
35398	19.95000000
35399	29.95000000
35401	18.95000000
35402	18.95000000
35403	14.95000000
35404	15.95000000
35405	15.50000000
35406	18.95000000
35407	18.95000000
35408	18.95000000
35409	18.95000000
35411	24.95000000
35412	20.95000000
35413	16.95000000
35414	18.95000000
35415	14.95000000
35416	15.95000000
35417	14.95000000
35418	22.95000000
35419	26.95000000
35420	15.95000000
35421	19.50000000
35423	18.95000000
35424	27.95000000
35426	27.95000000
35427	28.95000000
35428	9.95000000
35429	28.95000000
35430	21.95000000
35432	21.95000000
35433	29.95000000
35434	18.95000000
35435	14.95000000
35436	22.95000000
35437	16.95000000
35438	19.95000000
35439	24.95000000
35440	24.95000000
35441	31.95000000
35442	20.95000000
35443	18.95000000
35444	15.95000000
35445	15.95000000
35446	19.95000000
35447	19.95000000
35448	18.95000000
35449	15.95000000
35450	22.95000000
35451	15.95000000
35452	17.95000000
35453	31.95000000
35454	18.95000000
35455	31.95000000
35456	12.50000000
35457	9.95000000
35458	18.95000000
35459	15.95000000
35460	15.95000000
35461	16.45000000
35462	19.95000000
35463	27.95000000
35464	18.95000000
35466	26.95000000
35467	23.95000000
35468	18.95000000
35469	20.95000000
35471	19.95000000
35472	28.95000000
35473	18.95000000
35474	49.95000000
35475	18.95000000
35476	30.95000000
35477	17.95000000
35482	25.95000000
35483	19.95000000
35484	31.95000000
35485	13.95000000
35486	12.95000000
35488	18.95000000
35489	59.95000000
35490	12.95000000
35491	10.95000000
35492	19.95000000
35493	18.95000000
35495	18.95000000
35496	18.95000000
35497	14.95000000
35500	18.95000000
35502	15.95000000
35505	6.95000000
35506	26.95000000
35507	14.95000000
35508	18.95000000
35509	15.95000000
35510	15.95000000
35513	9.95000000
35514	25.95000000
35515	18.95000000
35516	12.95000000
35517	15.95000000
35518	18.95000000
35521	18.95000000
35523	23.95000000
35524	9.95000000
35526	15.95000000
35528	18.95000000
35529	15.95000000
35530	18.95000000
35532	15.95000000
35533	22.95000000
35535	18.95000000
35536	15.95000000
35537	9.95000000
35538	27.95000000
35539	13.95000000
35540	26.95000000
35541	16.95000000
35546	19.95000000
35548	27.95000000
35554	18.95000000
35555	18.95000000
35558	27.95000000
35559	18.95000000
35560	18.95000000
35561	14.95000000
35562	14.95000000
35568	10.00000000
35570	22.95000000
35571	19.95000000
35580	18.95000000
35583	13.95000000
35590	9.95000000
35596	16.95000000
35597	27.95000000
35602	15.50000000
35603	15.95000000
35604	15.95000000
35605	15.95000000
35610	18.95000000
35611	18.95000000
35612	18.95000000
35613	13.95000000
35614	13.95000000
35615	13.95000000
35616	18.95000000
35617	13.95000000
35618	13.95000000
35619	14.95000000
35621	13.95000000
35622	18.95000000
35623	13.95000000
35624	18.95000000
35625	18.95000000
35628	15.95000000
35630	18.95000000
35633	15.95000000
35636	15.50000000
35637	27.95000000
35638	27.95000000
35639	17.95000000
35645	18.95000000
35652	27.95000000
35653	18.95000000
35655	15.95000000
35656	18.95000000
35659	15.95000000
35666	9.95000000
35671	15.95000000
35672	10.95000000
35673	13.95000000
35674	18.95000000
35675	23.95000000
35677	27.95000000
35678	18.95000000
35679	15.95000000
35681	16.95000000
35682	9.95000000
35683	15.50000000
35684	16.95000000
35685	18.95000000
35688	15.95000000
35689	36.95000000
35690	24.95000000
35691	23.95000000
35692	13.95000000
35693	21.95000000
35694	59.95000000
35696	18.95000000
35697	17.95000000
35698	18.95000000
35699	15.95000000
35700	18.95000000
35701	18.95000000
35702	18.95000000
35703	13.95000000
35704	13.95000000
35705	13.95000000
35706	13.95000000
35707	18.95000000
35708	18.95000000
35710	15.95000000
35715	19.95000000
35724	18.95000000
35725	18.95000000
35726	15.95000000
35727	15.95000000
35728	27.95000000
35729	15.95000000
35730	14.95000000
35733	18.95000000
35735	19.95000000
35740	18.95000000
35741	15.95000000
35743	27.95000000
35744	15.95000000
35747	27.95000000
35748	18.95000000
35751	15.95000000
35755	17.95000000
35756	10.95000000
35758	18.95000000
35759	15.95000000
35772	15.95000000
35781	19.95000000
35782	13.95000000
35783	21.95000000
35796	17.95000000
35799	18.95000000
35800	28.95000000
35802	13.95000000
35806	15.95000000
35807	18.95000000
35808	18.95000000
35809	24.95000000
35810	16.95000000
35811	21.95000000
35812	17.95000000
35813	23.95000000
35815	18.95000000
35816	23.95000000
35822	18.95000000
35828	9.95000000
35830	15.95000000
35831	14.95000000
35832	18.95000000
35837	21.95000000
35838	19.95000000
35840	19.95000000
35841	19.95000000
35842	18.95000000
35845	17.95000000
35846	18.95000000
35848	15.95000000
35856	18.95000000
35862	18.95000000
35869	17.95000000
35875	24.95000000
35877	13.95000000
35878	18.95000000
35879	18.95000000
35891	18.95000000
35897	18.95000000
35898	19.95000000
35902	18.95000000
35904	18.95000000
35907	23.95000000
35915	22.95000000
35918	18.95000000
35925	17.95000000
35927	18.95000000
35934	18.95000000
35936	18.95000000
35937	15.95000000
35939	12.95000000
35943	18.95000000
35944	18.95000000
35948	16.95000000
35952	16.95000000
35964	19.95000000
35966	13.95000000
55606	18.95000000
55607	18.95000000