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 (
    1958, 1959, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1, 
    1957, 1892, 1956, 1955, 1954, 1953, 
    1870, 1948, 1745, 1952, 1951, 1950, 
    1949, 1924, 1947, 1906, 1946, 1945, 
    1944, 1943, 1939, 1927, 1942, 1900, 
    1941, 1940, 1907, 1938, 1937, 1936, 
    1935, 1934, 1933, 1932, 1931, 1930, 
    1928, 1929, 1911, 1925, 1926, 1901, 
    1908, 1923, 1922, 1921, 1909, 1920, 
    1919, 1918, 1917, 1916, 1915, 1914, 
    1883, 1913, 1912, 1910, 1903, 1902, 
    1905, 1904, 1872, 1899, 1898, 1897, 
    1896, 1873, 1895, 1894, 1893, 1891, 
    1659, 1890, 1889, 1888, 1887, 1871, 
    1885, 1886, 1884, 1869, 1882, 1881
  ) 
  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.00286

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_product_prices",
      "access_type": "range",
      "possible_keys": ["usergroup", "product_id", "lower_limit", "usergroup_id"],
      "key": "product_id",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "rows": 170,
      "filtered": 26.32652283,
      "index_condition": "cscart_product_prices.product_id in (1958,1959,2,3,4,5,6,7,8,9,10,1,1957,1892,1956,1955,1954,1953,1870,1948,1745,1952,1951,1950,1949,1924,1947,1906,1946,1945,1944,1943,1939,1927,1942,1900,1941,1940,1907,1938,1937,1936,1935,1934,1933,1932,1931,1930,1928,1929,1911,1925,1926,1901,1908,1923,1922,1921,1909,1920,1919,1918,1917,1916,1915,1914,1883,1913,1912,1910,1903,1902,1905,1904,1872,1899,1898,1897,1896,1873,1895,1894,1893,1891,1659,1890,1889,1888,1887,1871,1885,1886,1884,1869,1882,1881)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
1 99000.00000000
2 99000.00000000
3 99000.00000000
4 299000.00000000
5 295000.00000000
6 385000.00000000
7 352000.00000000
8 295000.00000000
9 79000.00000000
10 99500.00000000
1659 465000.00000000
1745 0.00000000
1869 28000.00000000
1870 70000.00000000
1871 48000.00000000
1872 0.00000000
1873 63000.00000000
1881 44000.00000000
1882 48000.00000000
1883 30000.00000000
1884 48000.00000000
1885 260000.00000000
1886 48000.00000000
1887 48000.00000000
1888 48000.00000000
1889 48000.00000000
1890 48000.00000000
1891 97000.00000000
1892 145000.00000000
1893 158000.00000000
1894 158000.00000000
1895 158000.00000000
1896 63000.00000000
1897 63000.00000000
1898 0.00000000
1899 0.00000000
1900 30000.00000000
1901 38000.00000000
1902 148000.00000000
1903 42000.00000000
1904 26000.00000000
1905 82000.00000000
1906 58000.00000000
1907 80000.00000000
1908 23500.00000000
1909 60000.00000000
1910 32000.00000000
1911 52000.00000000
1912 60000.00000000
1913 60000.00000000
1914 55000.00000000
1915 72000.00000000
1916 28000.00000000
1917 32000.00000000
1918 13000.00000000
1919 23500.00000000
1920 23500.00000000
1921 60000.00000000
1922 22500.00000000
1923 46000.00000000
1924 98000.00000000
1925 28000.00000000
1926 23000.00000000
1927 0.00000000
1928 23000.00000000
1929 28000.00000000
1930 23000.00000000
1931 23000.00000000
1932 23000.00000000
1933 23000.00000000
1934 23000.00000000
1935 22500.00000000
1936 23000.00000000
1937 23000.00000000
1938 22500.00000000
1939 44000.00000000
1940 62000.00000000
1941 62000.00000000
1942 30000.00000000
1943 44000.00000000
1944 30000.00000000
1945 70000.00000000
1946 23500.00000000
1947 100000.00000000
1948 50000.00000000
1949 145000.00000000
1950 270000.00000000
1951 135000.00000000
1952 165000.00000000
1953 17000.00000000
1954 17000.00000000
1955 95000.00000000
1956 40000.00000000
1957 70000.00000000
1958 0.00000000
1959 95000.00000000