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
  ) 
  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.00306

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": "usergroup",
      "key_length": "9",
      "used_key_parts": ["product_id", "usergroup_id", "lower_limit"],
      "rows": 96,
      "filtered": 100,
      "index_condition": "cscart_product_prices.lower_limit = 1 and 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) 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
1745 0.00000000
1870 70000.00000000
1892 145000.00000000
1900 30000.00000000
1906 58000.00000000
1907 80000.00000000
1924 98000.00000000
1927 0.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