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 (
    32019, 
    32119, 
    32177, 
    32142, 
    32153, 
    32036, 
    32195, 
    32199, 
    32164, 
    32121, 
    32257, 
    32116, 
    32127, 
    32002, 
    32151, 
    32000, 
    32175, 
    32230, 
    32176, 
    31951, 
    32236, 
    32220, 
    31974, 
    32173, 
    32024, 
    32251, 
    32135, 
    32183, 
    31954, 
    32137, 
    32004, 
    32240, 
    31985, 
    32034, 
    32191, 
    32005, 
    32238, 
    32250, 
    32088, 
    31934, 
    32237, 
    32032, 
    32201, 
    32197, 
    32033, 
    32131, 
    32009, 
    32185, 
    32204, 
    32027, 
    32294, 
    31984, 
    32120, 
    32205, 
    32182, 
    32155, 
    32156, 
    31927, 
    32110, 
    32208, 
    31944, 
    31950, 
    31962, 
    32087, 
    32228, 
    31976, 
    32162, 
    32161, 
    32295, 
    32262, 
    32165, 
    32192, 
    32108, 
    32296, 
    32303, 
    31948, 
    32130, 
    32020, 
    32150, 
    32167, 
    31981, 
    32022, 
    32030, 
    32041, 
    32227, 
    31983, 
    31940, 
    32293, 
    32095, 
    31994, 
    32255, 
    32082, 
    32217, 
    31972, 
    32169, 
    32070, 
    31942, 
    32073, 
    32013, 
    31953, 
    31971, 
    32076, 
    32099, 
    32122, 
    31932, 
    31957, 
    32168, 
    32180, 
    31959, 
    32016, 
    32212, 
    32051, 
    32247, 
    31947, 
    31931, 
    32113, 
    32149, 
    31928, 
    32198, 
    32049, 
    32299, 
    32145, 
    31930, 
    32279, 
    32297, 
    31977, 
    32112, 
    31929, 
    31961, 
    32174, 
    32301, 
    32115, 
    32055, 
    32053, 
    32179, 
    32181, 
    31926, 
    31992, 
    32071, 
    32081, 
    32097, 
    31956, 
    31975, 
    32042, 
    32152, 
    32235, 
    32011, 
    32107, 
    32109, 
    32264, 
    31941, 
    31964, 
    31966, 
    31970, 
    31982, 
    31986, 
    31999, 
    32023, 
    32038, 
    32047, 
    32050, 
    32080, 
    32083, 
    32084, 
    32093, 
    32100, 
    32114, 
    32124, 
    32190, 
    32219, 
    32252, 
    32218, 
    31922, 
    31923, 
    31924, 
    31936, 
    31938, 
    31939, 
    31955, 
    31969, 
    31989, 
    31998, 
    32015, 
    32018, 
    32025, 
    32029, 
    32039, 
    32040, 
    32056, 
    32057, 
    32063, 
    32065, 
    32072, 
    32074, 
    32079, 
    32090, 
    32091, 
    32094, 
    32096, 
    32103, 
    32126, 
    32143, 
    32147, 
    32160, 
    32186, 
    32203, 
    32221, 
    32223, 
    32232, 
    32234, 
    32243, 
    32246, 
    32258, 
    32263, 
    31925, 
    31933, 
    31949, 
    31963, 
    31965, 
    31968, 
    31973, 
    31979, 
    31997, 
    32007, 
    32008, 
    32021, 
    32048, 
    32052, 
    32058, 
    32059, 
    32062, 
    32064, 
    32066, 
    32067, 
    32068, 
    32075, 
    32077, 
    32078, 
    32085, 
    32089, 
    32092, 
    32101, 
    32104, 
    32106, 
    32111, 
    32118, 
    32123, 
    32134, 
    32141, 
    32194, 
    32200, 
    32207, 
    32209, 
    32210, 
    32211, 
    32215, 
    32224, 
    32225, 
    32229, 
    32242, 
    32253, 
    31921, 
    31937, 
    31952, 
    32001, 
    32014, 
    32069, 
    32086, 
    32102, 
    32105, 
    32128, 
    32132, 
    32154, 
    32184, 
    32213, 
    32239, 
    32244, 
    32292, 
    31990, 
    31991, 
    31996, 
    32054, 
    32117, 
    32133, 
    32226, 
    32163, 
    32010
  ) 
  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.00265

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "200.91"
    },
    "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": 287,
        "rows_produced_per_join": 57,
        "filtered": "20.00",
        "index_condition": "(`cscartdb`.`cscart_product_prices`.`product_id` in (32019,32119,32177,32142,32153,32036,32195,32199,32164,32121,32257,32116,32127,32002,32151,32000,32175,32230,32176,31951,32236,32220,31974,32173,32024,32251,32135,32183,31954,32137,32004,32240,31985,32034,32191,32005,32238,32250,32088,31934,32237,32032,32201,32197,32033,32131,32009,32185,32204,32027,32294,31984,32120,32205,32182,32155,32156,31927,32110,32208,31944,31950,31962,32087,32228,31976,32162,32161,32295,32262,32165,32192,32108,32296,32303,31948,32130,32020,32150,32167,31981,32022,32030,32041,32227,31983,31940,32293,32095,31994,32255,32082,32217,31972,32169,32070,31942,32073,32013,31953,31971,32076,32099,32122,31932,31957,32168,32180,31959,32016,32212,32051,32247,31947,31931,32113,32149,31928,32198,32049,32299,32145,31930,32279,32297,31977,32112,31929,31961,32174,32301,32115,32055,32053,32179,32181,31926,31992,32071,32081,32097,31956,31975,32042,32152,32235,32011,32107,32109,32264,31941,31964,31966,31970,31982,31986,31999,32023,32038,32047,32050,32080,32083,32084,32093,32100,32114,32124,32190,32219,32252,32218,31922,31923,31924,31936,31938,31939,31955,31969,31989,31998,32015,32018,32025,32029,32039,32040,32056,32057,32063,32065,32072,32074,32079,32090,32091,32094,32096,32103,32126,32143,32147,32160,32186,32203,32221,32223,32232,32234,32243,32246,32258,32263,31925,31933,31949,31963,31965,31968,31973,31979,31997,32007,32008,32021,32048,32052,32058,32059,32062,32064,32066,32067,32068,32075,32077,32078,32085,32089,32092,32101,32104,32106,32111,32118,32123,32134,32141,32194,32200,32207,32209,32210,32211,32215,32224,32225,32229,32242,32253,31921,31937,31952,32001,32014,32069,32086,32102,32105,32128,32132,32154,32184,32213,32239,32244,32292,31990,31991,31996,32054,32117,32133,32226,32163,32010))",
        "cost_info": {
          "read_cost": "195.17",
          "eval_cost": "5.74",
          "prefix_cost": "200.91",
          "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
31921	40.00000000
31922	34.00000000
31923	34.00000000
31924	34.00000000
31925	30.00000000
31926	40.00000000
31927	0.00000000
31928	33.00000000
31929	33.00000000
31930	40.00000000
31931	33.00000000
31932	62.00000000
31933	30.00000000
31934	0.00000000
31936	34.00000000
31937	30.00000000
31938	34.00000000
31939	28.00000000
31940	0.00000000
31941	40.00000000
31942	0.00000000
31944	67.00000000
31947	27.00000000
31948	27.00000000
31949	33.00000000
31950	0.00000000
31951	85.00000000
31952	40.00000000
31953	77.00000000
31954	77.00000000
31955	28.00000000
31956	40.00000000
31957	25.00000000
31959	33.00000000
31961	40.00000000
31962	0.00000000
31963	30.00000000
31964	30.00000000
31965	36.00000000
31966	34.00000000
31968	26.00000000
31969	30.00000000
31970	50.00000000
31971	25.00000000
31972	40.00000000
31973	30.00000000
31974	17.00000000
31975	40.00000000
31976	77.00000000
31977	29.00000000
31979	10.00000000
31981	0.00000000
31982	33.00000000
31983	0.00000000
31984	0.00000000
31985	0.00000000
31986	30.00000000
31989	26.00000000
31990	32.00000000
31991	36.00000000
31992	40.00000000
31994	0.00000000
31996	33.00000000
31997	33.00000000
31998	30.00000000
31999	26.00000000
32000	25.00000000
32001	30.00000000
32002	0.00000000
32004	87.00000000
32005	25.00000000
32007	35.00000000
32008	36.00000000
32009	0.00000000
32010	32.00000000
32011	34.00000000
32013	77.00000000
32014	45.00000000
32015	45.00000000
32016	33.00000000
32018	36.00000000
32019	22.00000000
32020	17.00000000
32021	30.00000000
32022	0.00000000
32023	36.00000000
32024	0.00000000
32025	32.00000000
32027	67.00000000
32029	26.00000000
32030	0.00000000
32032	0.00000000
32033	0.00000000
32034	70.00000000
32036	22.00000000
32038	30.00000000
32039	34.00000000
32040	34.00000000
32041	0.00000000
32042	26.00000000
32047	26.00000000
32048	45.00000000
32049	45.00000000
32050	30.00000000
32051	40.00000000
32052	30.00000000
32053	25.00000000
32054	33.00000000
32055	40.00000000
32056	36.00000000
32057	25.00000000
32058	35.00000000
32059	30.00000000
32062	36.00000000
32063	30.00000000
32064	30.00000000
32065	30.00000000
32066	36.00000000
32067	28.00000000
32068	34.00000000
32069	30.00000000
32070	0.00000000
32071	35.00000000
32072	30.00000000
32073	45.00000000
32074	30.00000000
32075	40.00000000
32076	0.00000000
32077	34.00000000
32078	30.00000000
32079	40.00000000
32080	34.00000000
32081	40.00000000
32082	0.00000000
32083	28.00000000
32084	36.00000000
32085	28.00000000
32086	30.00000000
32087	0.00000000
32088	0.00000000
32089	30.00000000
32090	45.00000000
32091	50.00000000
32092	34.00000000
32093	38.00000000
32094	30.00000000
32095	0.00000000
32096	34.00000000
32097	40.00000000
32099	0.00000000
32100	40.00000000
32101	40.00000000
32102	30.00000000
32103	34.00000000
32104	34.00000000
32105	30.00000000
32106	30.00000000
32107	40.00000000
32108	77.00000000
32109	40.00000000
32110	77.00000000
32111	34.00000000
32112	25.00000000
32113	25.00000000
32114	36.00000000
32115	45.00000000
32116	70.00000000
32117	32.00000000
32118	40.00000000
32119	22.00000000
32120	0.00000000
32121	17.00000000
32122	31.00000000
32123	30.00000000
32124	30.00000000
32126	50.00000000
32127	275.00000000
32128	40.00000000
32130	67.00000000
32131	77.00000000
32132	25.00000000
32133	30.00000000
32134	26.00000000
32135	425.00000000
32137	17.00000000
32141	34.00000000
32142	19.50000000
32143	25.00000000
32145	40.00000000
32147	26.00000000
32149	29.00000000
32150	22.00000000
32151	17.00000000
32152	40.00000000
32153	19.50000000
32154	26.00000000
32155	0.00000000
32156	85.00000000
32160	34.00000000
32161	87.00000000
32162	67.00000000
32163	36.00000000
32164	19.00000000
32165	0.00000000
32167	17.00000000
32168	87.00000000
32169	22.00000000
32173	55.00000000
32174	33.00000000
32175	0.00000000
32176	70.00000000
32177	17.00000000
32179	29.00000000
32180	29.00000000
32181	29.00000000
32182	17.00000000
32183	17.00000000
32184	38.00000000
32185	77.00000000
32186	35.00000000
32190	30.00000000
32191	0.00000000
32192	0.00000000
32194	25.00000000
32195	17.00000000
32197	85.00000000
32198	33.00000000
32199	17.00000000
32200	36.00000000
32201	0.00000000
32203	34.00000000
32204	0.00000000
32205	0.00000000
32207	26.00000000
32208	0.00000000
32209	25.00000000
32210	30.00000000
32211	30.00000000
32212	45.00000000
32213	40.00000000
32215	50.00000000
32217	62.00000000
32218	25.00000000
32219	40.00000000
32220	87.00000000
32221	34.00000000
32223	40.00000000
32224	32.00000000
32225	36.00000000
32226	40.00000000
32227	0.00000000
32228	0.00000000
32229	25.00000000
32230	17.00000000
32232	40.00000000
32234	38.00000000
32235	35.00000000
32236	70.00000000
32237	17.00000000
32238	0.00000000
32239	36.00000000
32240	85.00000000
32242	25.00000000
32243	40.00000000
32244	40.00000000
32246	50.00000000
32247	40.00000000
32250	17.00000000
32251	22.00000000
32252	34.00000000
32253	30.00000000
32255	0.00000000
32257	22.00000000
32258	38.00000000
32262	275.00000000
32263	30.00000000
32264	45.00000000
32279	40.00000000
32292	38.00000000
32293	33.00000000
32294	0.00000000
32295	77.00000000
32296	67.00000000
32297	40.00000000
32299	33.00000000
32301	33.00000000
32303	22.00000000