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 (
    5390, 5391, 5392, 5393, 5394, 5395, 5396, 
    5397, 5398, 5399, 5400, 5401, 5402, 
    5403, 5404, 5405, 5406, 5407, 5408, 
    5409, 5410, 5411, 5412, 5413, 5414, 
    6728, 515, 3156, 3676, 5131, 3834, 3833, 
    2130, 2131, 2132, 2133, 2134, 2135, 
    2136, 2137, 2138, 2139, 2140, 2141, 
    2142, 2143, 2144, 2145, 2146, 2147, 
    2148, 2149, 2150, 2151, 2152, 2153, 
    2154, 2155, 2156, 2157, 2158, 2159, 
    2160, 2161, 2162, 2163, 2164, 2165, 
    2166, 2167, 2168, 2169, 2170, 2171, 
    2172, 2173, 2174, 2175, 2176, 2177, 
    2178, 2179, 2180, 2181, 2182, 2183, 
    2184, 3779, 3780, 3781, 3782, 3783, 
    3784, 3785, 3786, 3787
  ) 
  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.00211

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "67.21"
    },
    "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": 96,
        "rows_produced_per_join": 19,
        "filtered": "20.00",
        "index_condition": "(`gaseus`.`cscart_product_prices`.`product_id` in (5390,5391,5392,5393,5394,5395,5396,5397,5398,5399,5400,5401,5402,5403,5404,5405,5406,5407,5408,5409,5410,5411,5412,5413,5414,6728,515,3156,3676,5131,3834,3833,2130,2131,2132,2133,2134,2135,2136,2137,2138,2139,2140,2141,2142,2143,2144,2145,2146,2147,2148,2149,2150,2151,2152,2153,2154,2155,2156,2157,2158,2159,2160,2161,2162,2163,2164,2165,2166,2167,2168,2169,2170,2171,2172,2173,2174,2175,2176,2177,2178,2179,2180,2181,2182,2183,2184,3779,3780,3781,3782,3783,3784,3785,3786,3787))",
        "cost_info": {
          "read_cost": "65.29",
          "eval_cost": "1.92",
          "prefix_cost": "67.21",
          "data_read_per_join": "460"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`gaseus`.`cscart_product_prices`.`lower_limit` = 1) and (`gaseus`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
515 0.00000000
2130 0.00000000
2131 0.00000000
2132 0.00000000
2133 0.00000000
2134 0.00000000
2135 0.00000000
2136 0.00000000
2137 0.00000000
2138 0.00000000
2139 0.00000000
2140 0.00000000
2141 0.00000000
2142 0.00000000
2143 0.00000000
2144 0.00000000
2145 0.00000000
2146 0.00000000
2147 0.00000000
2148 0.00000000
2149 0.00000000
2150 0.00000000
2151 0.00000000
2152 0.00000000
2153 0.00000000
2154 0.00000000
2155 0.00000000
2156 0.00000000
2157 0.00000000
2158 0.00000000
2159 0.00000000
2160 0.00000000
2161 0.00000000
2162 0.00000000
2163 0.00000000
2164 0.00000000
2165 0.00000000
2166 0.00000000
2167 0.00000000
2168 0.00000000
2169 0.00000000
2170 0.00000000
2171 0.00000000
2172 0.00000000
2173 0.00000000
2174 0.00000000
2175 0.00000000
2176 0.00000000
2177 0.00000000
2178 0.00000000
2179 0.00000000
2180 0.00000000
2181 0.00000000
2182 0.00000000
2183 0.00000000
2184 0.00000000
3156 772.00000000
3676 0.00000000
3779 0.00000000
3780 0.00000000
3781 0.00000000
3782 0.00000000
3783 0.00000000
3784 0.00000000
3785 0.00000000
3786 0.00000000
3787 0.00000000
3833 0.00000000
3834 0.00000000
5131 0.00000000
5390 0.00000000
5391 0.00000000
5392 0.00000000
5393 0.00000000
5394 0.00000000
5395 0.00000000
5396 0.00000000
5397 0.00000000
5398 0.00000000
5399 0.00000000
5400 0.00000000
5401 0.00000000
5402 0.00000000
5403 0.00000000
5404 0.00000000
5405 0.00000000
5406 0.00000000
5407 0.00000000
5408 0.00000000
5409 0.00000000
5410 0.00000000
5411 0.00000000
5412 0.00000000
5413 0.00000000
5414 0.00000000
6728 772.00000000