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 (
    30818, 
    30395, 
    30374, 
    30287, 
    30313, 
    30566, 
    30434, 
    30242, 
    30499, 
    30164, 
    30304, 
    30432, 
    30542, 
    30397, 
    30495, 
    30483, 
    30165, 
    30564, 
    30445, 
    30209, 
    30221, 
    30577, 
    30473, 
    30392, 
    30557, 
    30450, 
    30516, 
    30606, 
    30349, 
    30362, 
    30595, 
    30599, 
    30102, 
    30213, 
    30573, 
    30100, 
    30312, 
    30521, 
    30422, 
    30338, 
    30344, 
    30437, 
    30536, 
    30578, 
    30339, 
    30508, 
    30212, 
    30474, 
    30195, 
    30311, 
    30352, 
    30183, 
    30138, 
    30149, 
    30451, 
    30162, 
    30447, 
    30228, 
    30576, 
    30163, 
    30288, 
    30452, 
    30526, 
    30188, 
    30270, 
    30205, 
    30532, 
    30423, 
    30193, 
    30457, 
    30272, 
    30317, 
    30113, 
    30194, 
    30216, 
    30315, 
    30453, 
    30314, 
    30335, 
    30545, 
    30431, 
    30235, 
    30438, 
    30501, 
    30475, 
    30220, 
    30318, 
    30407, 
    30513, 
    30559, 
    30265, 
    30468, 
    30108, 
    30290, 
    30370, 
    30360, 
    30405, 
    30342, 
    30570, 
    30404, 
    30455, 
    30561, 
    30616, 
    30190, 
    30281, 
    30328, 
    30329, 
    30170, 
    30294, 
    30394, 
    30406, 
    30246, 
    30343, 
    30409, 
    30219, 
    30222, 
    30425, 
    30439, 
    30135, 
    30184, 
    30186, 
    30196, 
    30302, 
    30139, 
    30189, 
    30273, 
    30380, 
    30167, 
    30393, 
    30126, 
    30416, 
    30543, 
    30340, 
    30408, 
    30553, 
    30386, 
    30390, 
    30589, 
    30148, 
    30322, 
    30470, 
    30492, 
    30177, 
    30207, 
    30478, 
    30613, 
    30227, 
    30400, 
    30309, 
    30463, 
    30558, 
    30156, 
    30208, 
    30245, 
    30579, 
    30620, 
    30560, 
    30137, 
    30154, 
    30299, 
    30341, 
    30348, 
    30602, 
    30609, 
    30255, 
    30454, 
    30621, 
    30192, 
    30198, 
    30243, 
    30308, 
    30391, 
    30612, 
    30498, 
    30563, 
    30610, 
    30618, 
    30241, 
    30263, 
    30114, 
    30257, 
    30398, 
    30441, 
    30489, 
    30519, 
    30574, 
    30580, 
    30625, 
    30375, 
    30379, 
    30411, 
    30446, 
    30592, 
    30615, 
    30623, 
    30158, 
    30161, 
    30300, 
    30357, 
    30476, 
    30584, 
    30176, 
    30320, 
    30456, 
    30460, 
    30461, 
    30565, 
    30588, 
    30591, 
    30112, 
    30175, 
    30197, 
    30337, 
    30487, 
    30104, 
    30110, 
    30316, 
    30421, 
    30522, 
    30231, 
    30429, 
    30440, 
    30617, 
    30099, 
    30152, 
    30179, 
    30168, 
    30262, 
    30286, 
    30403, 
    30442, 
    30567, 
    30569, 
    30597, 
    30605, 
    30614, 
    30092, 
    30418, 
    30562, 
    30581, 
    30244, 
    30279, 
    30292, 
    30310, 
    30330, 
    30554, 
    30583, 
    30586, 
    30458, 
    30459, 
    30601, 
    30153, 
    30214, 
    30331, 
    30171, 
    30232, 
    30297, 
    30353, 
    30480, 
    30568, 
    30347, 
    30350, 
    30366, 
    30503, 
    30624, 
    30098, 
    30141, 
    30364, 
    30410, 
    30424, 
    30469, 
    30530, 
    30551, 
    30585, 
    30619, 
    30247, 
    30256, 
    30399, 
    30412, 
    30511, 
    30172, 
    30203, 
    30252, 
    30266, 
    30464, 
    30479, 
    30598, 
    30334, 
    30383, 
    30387, 
    30402, 
    30467, 
    30510, 
    30608, 
    30103, 
    30301, 
    30507, 
    30523, 
    30107, 
    30115, 
    30116, 
    30142, 
    30169, 
    30199, 
    30206, 
    30111, 
    30120, 
    30224, 
    30254, 
    30280, 
    30305, 
    30202, 
    30283, 
    30396, 
    30433, 
    30502, 
    30524, 
    30128, 
    30389, 
    30611, 
    30151, 
    30275, 
    30471, 
    30531, 
    30552, 
    30607, 
    30260, 
    30327, 
    30106, 
    30182, 
    30204, 
    30226, 
    30230, 
    30274, 
    30278, 
    30285, 
    30346, 
    30377, 
    30512, 
    30529, 
    30571, 
    30465, 
    30105, 
    30200, 
    30361, 
    30354, 
    30359, 
    30443, 
    30514, 
    30269, 
    30117, 
    30218, 
    30368, 
    30382, 
    30372, 
    30518, 
    30417, 
    30504, 
    30587, 
    30140, 
    30150, 
    30180, 
    30181, 
    30217, 
    30264, 
    30277, 
    30321, 
    30240, 
    30215, 
    30271, 
    30101, 
    30427, 
    30477, 
    30500, 
    30155, 
    30259, 
    30378, 
    30324, 
    30159, 
    30178, 
    30234, 
    30355, 
    30462, 
    30261, 
    30109, 
    30494, 
    30282, 
    30157, 
    30326, 
    30535, 
    30537, 
    30118
  ) 
  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.00345

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "274.41"
    },
    "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": 392,
        "rows_produced_per_join": 78,
        "filtered": "20.00",
        "index_condition": "(`cscartdb`.`cscart_product_prices`.`product_id` in (30818,30395,30374,30287,30313,30566,30434,30242,30499,30164,30304,30432,30542,30397,30495,30483,30165,30564,30445,30209,30221,30577,30473,30392,30557,30450,30516,30606,30349,30362,30595,30599,30102,30213,30573,30100,30312,30521,30422,30338,30344,30437,30536,30578,30339,30508,30212,30474,30195,30311,30352,30183,30138,30149,30451,30162,30447,30228,30576,30163,30288,30452,30526,30188,30270,30205,30532,30423,30193,30457,30272,30317,30113,30194,30216,30315,30453,30314,30335,30545,30431,30235,30438,30501,30475,30220,30318,30407,30513,30559,30265,30468,30108,30290,30370,30360,30405,30342,30570,30404,30455,30561,30616,30190,30281,30328,30329,30170,30294,30394,30406,30246,30343,30409,30219,30222,30425,30439,30135,30184,30186,30196,30302,30139,30189,30273,30380,30167,30393,30126,30416,30543,30340,30408,30553,30386,30390,30589,30148,30322,30470,30492,30177,30207,30478,30613,30227,30400,30309,30463,30558,30156,30208,30245,30579,30620,30560,30137,30154,30299,30341,30348,30602,30609,30255,30454,30621,30192,30198,30243,30308,30391,30612,30498,30563,30610,30618,30241,30263,30114,30257,30398,30441,30489,30519,30574,30580,30625,30375,30379,30411,30446,30592,30615,30623,30158,30161,30300,30357,30476,30584,30176,30320,30456,30460,30461,30565,30588,30591,30112,30175,30197,30337,30487,30104,30110,30316,30421,30522,30231,30429,30440,30617,30099,30152,30179,30168,30262,30286,30403,30442,30567,30569,30597,30605,30614,30092,30418,30562,30581,30244,30279,30292,30310,30330,30554,30583,30586,30458,30459,30601,30153,30214,30331,30171,30232,30297,30353,30480,30568,30347,30350,30366,30503,30624,30098,30141,30364,30410,30424,30469,30530,30551,30585,30619,30247,30256,30399,30412,30511,30172,30203,30252,30266,30464,30479,30598,30334,30383,30387,30402,30467,30510,30608,30103,30301,30507,30523,30107,30115,30116,30142,30169,30199,30206,30111,30120,30224,30254,30280,30305,30202,30283,30396,30433,30502,30524,30128,30389,30611,30151,30275,30471,30531,30552,30607,30260,30327,30106,30182,30204,30226,30230,30274,30278,30285,30346,30377,30512,30529,30571,30465,30105,30200,30361,30354,30359,30443,30514,30269,30117,30218,30368,30382,30372,30518,30417,30504,30587,30140,30150,30180,30181,30217,30264,30277,30321,30240,30215,30271,30101,30427,30477,30500,30155,30259,30378,30324,30159,30178,30234,30355,30462,30261,30109,30494,30282,30157,30326,30535,30537,30118))",
        "cost_info": {
          "read_cost": "266.57",
          "eval_cost": "7.84",
          "prefix_cost": "274.41",
          "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
30092	39.00000000
30098	30.00000000
30099	40.00000000
30100	50.00000000
30101	44.00000000
30102	44.00000000
30103	39.00000000
30104	44.00000000
30105	44.00000000
30106	44.00000000
30107	39.00000000
30108	39.00000000
30109	44.00000000
30110	44.00000000
30111	37.00000000
30112	45.00000000
30113	39.00000000
30114	39.00000000
30115	39.00000000
30116	39.00000000
30117	44.00000000
30118	40.00000000
30120	44.00000000
30126	45.00000000
30128	50.00000000
30135	39.00000000
30137	30.00000000
30138	45.00000000
30139	45.00000000
30140	55.00000000
30141	55.00000000
30142	45.00000000
30148	55.00000000
30149	22.00000000
30150	34.00000000
30151	40.00000000
30152	37.00000000
30153	42.00000000
30154	45.00000000
30155	50.00000000
30156	55.00000000
30157	40.00000000
30158	45.00000000
30159	39.00000000
30161	33.00000000
30162	55.00000000
30163	34.00000000
30164	45.00000000
30165	44.00000000
30167	55.00000000
30168	50.00000000
30169	44.00000000
30170	37.00000000
30171	42.00000000
30172	45.00000000
30175	45.00000000
30176	50.00000000
30177	45.00000000
30178	70.00000000
30179	45.00000000
30180	37.00000000
30181	54.00000000
30182	39.00000000
30183	50.00000000
30184	55.00000000
30186	20.00000000
30188	40.00000000
30189	55.00000000
30190	45.00000000
30192	55.00000000
30193	45.00000000
30194	25.00000000
30195	39.00000000
30196	40.00000000
30197	45.00000000
30198	50.00000000
30199	43.00000000
30200	39.00000000
30202	55.00000000
30203	45.00000000
30204	44.00000000
30205	44.00000000
30206	39.00000000
30207	45.00000000
30208	37.00000000
30209	40.00000000
30212	70.00000000
30213	114.00000000
30214	20.00000000
30215	70.00000000
30216	45.00000000
30217	40.00000000
30218	40.00000000
30219	50.00000000
30220	55.00000000
30221	55.00000000
30222	55.00000000
30224	39.00000000
30226	41.00000000
30227	55.00000000
30228	45.00000000
30230	38.00000000
30231	10.00000000
30232	38.00000000
30234	39.00000000
30235	45.00000000
30240	39.00000000
30241	44.00000000
30242	50.00000000
30243	55.00000000
30244	44.00000000
30245	33.00000000
30246	50.00000000
30247	40.00000000
30252	37.00000000
30254	39.00000000
30255	45.00000000
30256	55.00000000
30257	44.00000000
30259	91.00000000
30260	40.00000000
30261	44.00000000
30262	30.00000000
30263	37.00000000
30264	44.00000000
30265	39.00000000
30266	39.00000000
30269	55.00000000
30270	50.00000000
30271	37.00000000
30272	41.00000000
30273	39.00000000
30274	38.00000000
30275	38.00000000
30277	39.00000000
30278	40.00000000
30279	39.00000000
30280	39.00000000
30281	50.00000000
30282	70.00000000
30283	38.00000000
30285	39.00000000
30286	39.00000000
30287	55.00000000
30288	45.00000000
30290	45.00000000
30292	45.00000000
30294	39.00000000
30297	39.00000000
30299	40.00000000
30300	22.00000000
30301	22.00000000
30302	40.00000000
30304	30.00000000
30305	44.00000000
30308	40.00000000
30309	55.00000000
30310	42.00000000
30311	55.00000000
30312	37.00000000
30313	40.00000000
30314	39.00000000
30315	40.00000000
30316	44.00000000
30317	55.00000000
30318	45.00000000
30320	39.00000000
30321	41.00000000
30322	39.00000000
30324	75.00000000
30326	22.00000000
30327	41.00000000
30328	30.00000000
30329	30.00000000
30330	39.00000000
30331	39.00000000
30334	65.00000000
30335	55.00000000
30337	38.00000000
30338	55.00000000
30339	55.00000000
30340	40.00000000
30341	45.00000000
30342	45.00000000
30343	45.00000000
30344	45.00000000
30346	39.00000000
30347	40.00000000
30348	39.00000000
30349	70.00000000
30350	39.00000000
30352	55.00000000
30353	45.00000000
30354	41.00000000
30355	39.00000000
30357	38.00000000
30359	37.00000000
30360	40.00000000
30361	38.00000000
30362	40.00000000
30364	35.00000000
30366	39.00000000
30368	39.00000000
30370	42.00000000
30372	44.00000000
30374	40.00000000
30375	37.00000000
30377	36.00000000
30378	40.00000000
30379	39.00000000
30380	44.00000000
30382	44.00000000
30383	44.00000000
30386	35.00000000
30387	40.00000000
30389	38.00000000
30390	37.00000000
30391	50.00000000
30392	60.00000000
30393	40.00000000
30394	37.00000000
30395	42.00000000
30396	40.00000000
30397	40.00000000
30398	40.00000000
30399	61.00000000
30400	40.00000000
30402	40.00000000
30403	37.00000000
30404	37.00000000
30405	35.00000000
30406	22.00000000
30407	55.00000000
30408	40.00000000
30409	40.00000000
30410	42.00000000
30411	38.00000000
30412	38.00000000
30416	44.00000000
30417	42.00000000
30418	44.00000000
30421	22.00000000
30422	45.00000000
30423	40.00000000
30424	22.00000000
30425	42.00000000
30427	22.00000000
30429	55.00000000
30431	44.00000000
30432	40.00000000
30433	37.00000000
30434	40.00000000
30437	30.00000000
30438	38.00000000
30439	40.00000000
30440	40.00000000
30441	39.00000000
30442	39.00000000
30443	22.00000000
30445	38.00000000
30446	38.00000000
30447	37.00000000
30450	55.00000000
30451	40.00000000
30452	41.00000000
30453	44.00000000
30454	55.00000000
30455	39.00000000
30456	44.00000000
30457	38.00000000
30458	41.00000000
30459	41.00000000
30460	22.00000000
30461	39.00000000
30462	37.00000000
30463	37.00000000
30464	22.00000000
30465	22.00000000
30467	38.00000000
30468	45.00000000
30469	22.00000000
30470	55.00000000
30471	38.00000000
30473	40.00000000
30474	38.00000000
30475	45.00000000
30476	40.00000000
30477	30.00000000
30478	40.00000000
30479	60.00000000
30480	39.00000000
30483	50.00000000
30487	38.00000000
30489	44.00000000
30492	39.00000000
30494	39.00000000
30495	48.00000000
30498	37.00000000
30499	44.00000000
30500	41.00000000
30501	32.00000000
30502	45.00000000
30503	30.00000000
30504	65.00000000
30507	40.00000000
30508	40.00000000
30510	39.00000000
30511	39.00000000
30512	39.00000000
30513	39.00000000
30514	39.00000000
30516	39.00000000
30518	41.00000000
30519	39.00000000
30521	40.00000000
30522	40.00000000
30523	51.00000000
30524	39.00000000
30526	22.00000000
30529	44.00000000
30530	38.00000000
30531	44.00000000
30532	31.00000000
30535	70.00000000
30536	39.00000000
30537	41.00000000
30542	39.00000000
30543	39.00000000
30545	40.00000000
30551	34.00000000
30552	39.00000000
30553	41.00000000
30554	39.00000000
30557	43.00000000
30558	38.00000000
30559	41.00000000
30560	39.00000000
30561	38.00000000
30562	38.00000000
30563	20.00000000
30564	40.00000000
30565	41.00000000
30566	41.00000000
30567	41.00000000
30568	41.00000000
30569	41.00000000
30570	55.00000000
30571	38.00000000
30573	39.00000000
30574	51.00000000
30576	39.00000000
30577	40.00000000
30578	40.00000000
30579	44.00000000
30580	38.00000000
30581	39.00000000
30583	37.00000000
30584	54.00000000
30585	39.00000000
30586	39.00000000
30587	37.00000000
30588	51.00000000
30589	40.00000000
30591	39.00000000
30592	39.00000000
30595	41.00000000
30597	41.00000000
30598	22.00000000
30599	50.00000000
30601	35.00000000
30602	41.00000000
30605	38.00000000
30606	38.00000000
30607	44.00000000
30608	44.00000000
30609	44.00000000
30610	35.00000000
30611	41.00000000
30612	44.00000000
30613	39.00000000
30614	37.00000000
30615	70.00000000
30616	40.00000000
30617	40.00000000
30618	39.00000000
30619	41.00000000
30620	38.00000000
30621	35.00000000
30623	39.00000000
30624	41.00000000
30625	55.00000000
30818	45.00000000