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 (
    1906, 1907, 1908, 1909, 1910, 1911, 1912, 
    1913, 1914, 1915, 1916, 1917, 1918, 
    1919, 1920, 1921, 1922, 1923, 1924, 
    1925, 1926, 1927, 1928, 1929, 1930, 
    1931, 5560, 5561, 5562, 5563, 5564, 
    5565, 5566, 5567, 5568, 5569, 5570, 
    5571, 5572, 5573, 5574, 5575, 5576, 
    5577, 5578, 5579, 5580, 5581
  ) 
  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.00106

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.61"
    },
    "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": 48,
        "rows_produced_per_join": 9,
        "filtered": "20.00",
        "index_condition": "(`gaseus`.`cscart_product_prices`.`product_id` in (1906,1907,1908,1909,1910,1911,1912,1913,1914,1915,1916,1917,1918,1919,1920,1921,1922,1923,1924,1925,1926,1927,1928,1929,1930,1931,5560,5561,5562,5563,5564,5565,5566,5567,5568,5569,5570,5571,5572,5573,5574,5575,5576,5577,5578,5579,5580,5581))",
        "cost_info": {
          "read_cost": "32.65",
          "eval_cost": "0.96",
          "prefix_cost": "33.61",
          "data_read_per_join": "230"
        },
        "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
1906 0.00000000
1907 0.00000000
1908 0.00000000
1909 0.00000000
1910 0.00000000
1911 0.00000000
1912 0.00000000
1913 0.00000000
1914 0.00000000
1915 0.00000000
1916 0.00000000
1917 0.00000000
1918 0.00000000
1919 0.00000000
1920 0.00000000
1921 0.00000000
1922 0.00000000
1923 0.00000000
1924 0.00000000
1925 0.00000000
1926 0.00000000
1927 0.00000000
1928 0.00000000
1929 0.00000000
1930 0.00000000
1931 0.00000000
5560 0.00000000
5561 0.00000000
5562 0.00000000
5563 0.00000000
5564 0.00000000
5565 0.00000000
5566 0.00000000
5567 0.00000000
5568 0.00000000
5569 0.00000000
5570 0.00000000
5571 0.00000000
5572 0.00000000
5573 0.00000000
5574 0.00000000
5575 0.00000000
5576 0.00000000
5577 0.00000000
5578 0.00000000
5579 0.00000000
5580 0.00000000
5581 0.00000000