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 (
    2187, 
    2244, 
    2172, 
    2171, 
    1668, 
    8586, 
    8372, 
    2222, 
    2175, 
    2228, 
    2220, 
    2177, 
    2230, 
    2226, 
    1667, 
    2221, 
    2179, 
    1666, 
    8240, 
    2183, 
    8813, 
    2176, 
    8433, 
    2224, 
    2195, 
    2225, 
    8514, 
    2189, 
    2196, 
    2186, 
    2180, 
    2178, 
    2191, 
    2170, 
    2181, 
    2190, 
    2185, 
    2198, 
    2182, 
    2188, 
    2173, 
    8784, 
    2193, 
    2184, 
    2197, 
    2174, 
    2199, 
    2219, 
    2223, 
    2194, 
    2243, 
    2200, 
    2229, 
    8789, 
    8695, 
    8766, 
    8802, 
    8362, 
    8573, 
    8696, 
    8243, 
    8617, 
    8601, 
    8375, 
    8570, 
    8812, 
    8536, 
    8604, 
    8589, 
    8699, 
    8590, 
    8612, 
    8800, 
    8702, 
    8576, 
    8701, 
    8464, 
    8469, 
    8577, 
    8708, 
    8703, 
    8783, 
    8804, 
    8275, 
    8569, 
    8724, 
    8445, 
    8470, 
    8435, 
    8801, 
    8805, 
    8811, 
    8815, 
    8441, 
    8446, 
    8543, 
    8245, 
    8440, 
    8519, 
    8593, 
    8595, 
    8432, 
    8444, 
    8471, 
    8594, 
    8472, 
    8457, 
    8806, 
    8828, 
    8830, 
    8575, 
    8269, 
    8466, 
    8363, 
    8459, 
    8460, 
    8603, 
    8581, 
    8709, 
    8438, 
    8799, 
    8831, 
    8241, 
    8272, 
    8273, 
    8276, 
    8365, 
    8382, 
    8538, 
    8572, 
    8578, 
    8580, 
    8585, 
    8597, 
    8614, 
    8615, 
    8729, 
    8730, 
    8757, 
    8797, 
    8810, 
    8814, 
    8829, 
    8242, 
    8244, 
    8277, 
    8280, 
    8373, 
    8377, 
    8403, 
    8431, 
    8434, 
    8443, 
    8517, 
    8540, 
    8574, 
    8587, 
    8596, 
    8600, 
    8605
  ) 
  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.00284

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "112.01"
    },
    "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": 160,
        "rows_produced_per_join": 31,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (2187,2244,2172,2171,1668,8586,8372,2222,2175,2228,2220,2177,2230,2226,1667,2221,2179,1666,8240,2183,8813,2176,8433,2224,2195,2225,8514,2189,2196,2186,2180,2178,2191,2170,2181,2190,2185,2198,2182,2188,2173,8784,2193,2184,2197,2174,2199,2219,2223,2194,2243,2200,2229,8789,8695,8766,8802,8362,8573,8696,8243,8617,8601,8375,8570,8812,8536,8604,8589,8699,8590,8612,8800,8702,8576,8701,8464,8469,8577,8708,8703,8783,8804,8275,8569,8724,8445,8470,8435,8801,8805,8811,8815,8441,8446,8543,8245,8440,8519,8593,8595,8432,8444,8471,8594,8472,8457,8806,8828,8830,8575,8269,8466,8363,8459,8460,8603,8581,8709,8438,8799,8831,8241,8272,8273,8276,8365,8382,8538,8572,8578,8580,8585,8597,8614,8615,8729,8730,8757,8797,8810,8814,8829,8242,8244,8277,8280,8373,8377,8403,8431,8434,8443,8517,8540,8574,8587,8596,8600,8605))",
        "cost_info": {
          "read_cost": "108.81",
          "eval_cost": "3.20",
          "prefix_cost": "112.01",
          "data_read_per_join": "767"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`lower_limit` = 1) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id	price
1666	45000.00000000
1667	44000.00000000
1668	74000.00000000
2170	14000.00000000
2171	18000.00000000
2172	18000.00000000
2173	11750.00000000
2174	11750.00000000
2175	21000.00000000
2176	21000.00000000
2177	15000.00000000
2178	22000.00000000
2179	29000.00000000
2180	29000.00000000
2181	29000.00000000
2182	29000.00000000
2183	34000.00000000
2184	97500.00000000
2185	57000.00000000
2186	53500.00000000
2187	53000.00000000
2188	44000.00000000
2189	29000.00000000
2190	29000.00000000
2191	45500.00000000
2193	27000.00000000
2194	35000.00000000
2195	43000.00000000
2196	26000.00000000
2197	44000.00000000
2198	45500.00000000
2199	69000.00000000
2200	89000.00000000
2219	59000.00000000
2220	110000.00000000
2221	65000.00000000
2222	39000.00000000
2223	38000.00000000
2224	18000.00000000
2225	23000.00000000
2226	22000.00000000
2228	19000.00000000
2229	25000.00000000
2230	14000.00000000
2243	20000.00000000
2244	44000.00000000
8240	24000.00000000
8241	23000.00000000
8242	32000.00000000
8243	36000.00000000
8244	29000.00000000
8245	39000.00000000
8269	17000.00000000
8272	32000.00000000
8273	14000.00000000
8275	33000.00000000
8276	14000.00000000
8277	29000.00000000
8280	39000.00000000
8362	79000.00000000
8363	74000.00000000
8365	21000.00000000
8372	17000.00000000
8373	14000.00000000
8375	18000.00000000
8377	24000.00000000
8382	23000.00000000
8403	12000.00000000
8431	12000.00000000
8432	39000.00000000
8433	79000.00000000
8434	44000.00000000
8435	14000.00000000
8438	51000.00000000
8440	35000.00000000
8441	35000.00000000
8443	25000.00000000
8444	35000.00000000
8445	35000.00000000
8446	35000.00000000
8457	35000.00000000
8459	110000.00000000
8460	59000.00000000
8464	35000.00000000
8466	39000.00000000
8469	35000.00000000
8470	38000.00000000
8471	35000.00000000
8472	33000.00000000
8514	29000.00000000
8517	33000.00000000
8519	44000.00000000
8536	83000.00000000
8538	79000.00000000
8540	79000.00000000
8543	44000.00000000
8569	18000.00000000
8570	44000.00000000
8572	55000.00000000
8573	59000.00000000
8574	66000.00000000
8575	79000.00000000
8576	19000.00000000
8577	23000.00000000
8578	49000.00000000
8580	55000.00000000
8581	59000.00000000
8585	21000.00000000
8586	59000.00000000
8587	29000.00000000
8589	33000.00000000
8590	44000.00000000
8593	29000.00000000
8594	29000.00000000
8595	24000.00000000
8596	15000.00000000
8597	15000.00000000
8600	119000.00000000
8601	98000.00000000
8603	23000.00000000
8604	18000.00000000
8605	38000.00000000
8612	29000.00000000
8614	53000.00000000
8615	23000.00000000
8617	44000.00000000
8695	38000.00000000
8696	29000.00000000
8699	89000.00000000
8701	284000.00000000
8702	359000.00000000
8703	29000.00000000
8708	59000.00000000
8709	53000.00000000
8724	29000.00000000
8729	15000.00000000
8730	29000.00000000
8757	68000.00000000
8766	59000.00000000
8783	49000.00000000
8784	9000.00000000
8789	119000.00000000
8797	25000.00000000
8799	74000.00000000
8800	14000.00000000
8801	17000.00000000
8802	289000.00000000
8804	134000.00000000
8805	81000.00000000
8806	44000.00000000
8810	29000.00000000
8811	18000.00000000
8812	38000.00000000
8813	27000.00000000
8814	23000.00000000
8815	29000.00000000
8828	29000.00000000
8829	23000.00000000
8830	48000.00000000
8831	18000.00000000