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 (
    1987, 
    2110, 
    2111, 
    2112, 
    1900, 
    1813, 
    1820, 
    2366, 
    1897, 
    1750, 
    1924, 
    2227, 
    2204, 
    2010, 
    2309, 
    2060, 
    2122, 
    2274, 
    2061, 
    1918, 
    1913, 
    1958, 
    2290, 
    1751, 
    1957, 
    2202, 
    1814, 
    2331, 
    1748, 
    2005, 
    2214, 
    2342, 
    2215, 
    2211, 
    2225, 
    2212, 
    2213, 
    2226, 
    2151, 
    1882, 
    1815, 
    1853, 
    1759, 
    2001, 
    2276, 
    2231, 
    2281, 
    2379, 
    2388, 
    2115, 
    1929, 
    2373, 
    2372, 
    2374, 
    1760, 
    1807, 
    1847, 
    2363, 
    1816, 
    2146, 
    2201, 
    2386, 
    2359, 
    2279, 
    1843, 
    1859, 
    1794, 
    1793, 
    1817, 
    1795, 
    2278, 
    1818, 
    1784, 
    1819, 
    1849, 
    2338, 
    2383, 
    2337, 
    2336, 
    2389, 
    1802, 
    1801, 
    1880, 
    1767, 
    2346, 
    2301, 
    2081, 
    1947, 
    2157, 
    2207, 
    2142, 
    2121, 
    2375, 
    2088, 
    2089, 
    2090, 
    2087, 
    2158, 
    1787, 
    2105, 
    2133, 
    2125, 
    2092, 
    2093, 
    2046, 
    2045, 
    2044, 
    2047, 
    2049, 
    2048, 
    1995, 
    1943, 
    2120, 
    2094, 
    2385, 
    1989, 
    2314, 
    1951, 
    2096, 
    1988, 
    2098, 
    2097, 
    2099, 
    2199, 
    2200, 
    1948, 
    2095, 
    2100, 
    2124, 
    2101, 
    2134, 
    2198, 
    2291, 
    2103, 
    2307, 
    2166, 
    2208, 
    2102, 
    2104, 
    2345, 
    2143, 
    2119, 
    2144, 
    2362, 
    2233, 
    2350, 
    2232, 
    2244, 
    2051, 
    1855, 
    2367, 
    1808, 
    1891, 
    1892, 
    2368, 
    2393, 
    1952, 
    1939, 
    1930, 
    1764
  ) 
  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.00085

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "116.51"
    },
    "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": 170,
        "rows_produced_per_join": 33,
        "filtered": "19.99",
        "index_condition": "(`cscartdevel`.`cscart_product_prices`.`product_id` in (1987,2110,2111,2112,1900,1813,1820,2366,1897,1750,1924,2227,2204,2010,2309,2060,2122,2274,2061,1918,1913,1958,2290,1751,1957,2202,1814,2331,1748,2005,2214,2342,2215,2211,2225,2212,2213,2226,2151,1882,1815,1853,1759,2001,2276,2231,2281,2379,2388,2115,1929,2373,2372,2374,1760,1807,1847,2363,1816,2146,2201,2386,2359,2279,1843,1859,1794,1793,1817,1795,2278,1818,1784,1819,1849,2338,2383,2337,2336,2389,1802,1801,1880,1767,2346,2301,2081,1947,2157,2207,2142,2121,2375,2088,2089,2090,2087,2158,1787,2105,2133,2125,2092,2093,2046,2045,2044,2047,2049,2048,1995,1943,2120,2094,2385,1989,2314,1951,2096,1988,2098,2097,2099,2199,2200,1948,2095,2100,2124,2101,2134,2198,2291,2103,2307,2166,2208,2102,2104,2345,2143,2119,2144,2362,2233,2350,2232,2244,2051,1855,2367,1808,1891,1892,2368,2393,1952,1939,1930,1764))",
        "cost_info": {
          "read_cost": "113.11",
          "eval_cost": "3.40",
          "prefix_cost": "116.51",
          "data_read_per_join": "815"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`cscartdevel`.`cscart_product_prices`.`lower_limit` = 1) and (`cscartdevel`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
1748 13.12000000
1750 14.94000000
1751 27.67000000
1759 9.96000000
1760 17.65000000
1764 7.58000000
1767 8.29000000
1784 11.29000000
1787 22.16000000
1793 27.83000000
1794 11.35000000
1795 13.94000000
1801 10.12000000
1802 7.98000000
1807 40.84000000
1808 77.48000000
1813 9.41000000
1814 7.25000000
1815 8.85000000
1816 6.87000000
1817 12.28000000
1818 9.96000000
1819 14.94000000
1820 13.06000000
1843 10.30000000
1847 32.09000000
1849 35.98000000
1853 70.41000000
1855 15.16000000
1859 11.40000000
1880 8.52000000
1882 17.93000000
1891 9.96000000
1892 21.03000000
1897 8.85000000
1900 16.60000000
1913 5.69000000
1918 8.69000000
1924 31.38000000
1929 11.18000000
1930 6.87000000
1939 8.17000000
1943 6.40000000
1947 13.80000000
1948 12.07000000
1951 14.30000000
1952 9.09000000
1957 13.60000000
1958 9.63000000
1987 12.90000000
1988 9.47000000
1989 8.22000000
1995 6.92000000
2001 10.03000000
2005 5.28000000
2010 9.74000000
2044 6.76000000
2045 6.58000000
2046 6.58000000
2047 7.59000000
2048 7.12000000
2049 6.58000000
2051 18.82000000
2060 3.40000000
2061 3.70000000
2081 39.87000000
2087 17.18000000
2088 21.45000000
2089 19.38000000
2090 24.19000000
2092 13.26000000
2093 11.65000000
2094 11.04000000
2095 12.13000000
2096 12.13000000
2097 11.65000000
2098 11.65000000
2099 11.65000000
2100 11.65000000
2101 11.65000000
2102 11.79000000
2103 12.13000000
2104 11.65000000
2105 13.80000000
2110 9.14000000
2111 11.20000000
2112 9.68000000
2115 12.37000000
2119 11.65000000
2120 12.13000000
2121 13.80000000
2122 3.40000000
2124 12.13000000
2125 13.80000000
2133 11.54000000
2134 11.65000000
2142 117.04000000
2143 117.08000000
2144 101.38000000
2146 21.65000000
2151 49.70000000
2157 31.47000000
2158 31.47000000
2166 31.99000000
2198 12.13000000
2199 12.13000000
2200 12.13000000
2201 49.19000000
2202 33.49000000
2204 45.99000000
2207 115.47000000
2208 100.74000000
2211 7.98000000
2212 7.98000000
2213 7.98000000
2214 7.98000000
2215 5.23000000
2225 12.32000000
2226 12.32000000
2227 6.59000000
2231 19.32000000
2232 19.32000000
2233 36.75000000
2244 46.05000000
2274 3.99000000
2276 7.68000000
2278 7.45000000
2279 7.98000000
2281 7.00000000
2290 25.08000000
2291 13.80000000
2301 13.74000000
2307 10.01000000
2309 27.41000000
2314 12.13000000
2331 6.21000000
2336 26.58000000
2337 26.58000000
2338 29.16000000
2342 9.51000000
2345 12.13000000
2346 13.80000000
2350 10.81000000
2359 20.33000000
2362 12.13000000
2363 5.04000000
2366 18.24000000
2367 24.23000000
2368 21.65000000
2372 21.65000000
2373 20.83000000
2374 18.24000000
2375 18.01000000
2379 19.19000000
2383 8.56000000
2385 11.52000000
2386 33.88000000
2388 19.01000000
2389 33.74000000
2393 34.51000000