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 (
    352386, 358695, 351371, 354344, 355656, 
    355717, 355719, 357488, 352231, 354808, 
    356679, 357144, 357567, 358789, 351261, 
    355919, 357569, 359418, 352382, 351602, 
    355645, 355142, 358320, 351445, 354347, 
    355723, 357136, 351377, 355661, 357611, 
    358359, 358477, 358540, 358877, 352477, 
    354416, 356003, 356241, 356526, 358730, 
    352616, 352754, 356383, 356426, 355938, 
    357139, 358395, 358769, 349759, 351301, 
    351349, 351818, 352701, 358389, 358438, 
    358544, 356680, 357524, 349747, 352680, 
    352327, 355856, 356432, 357525, 358384, 
    351405, 351413, 351546, 352588, 354456, 
    354837, 355026, 355918, 356619, 350179, 
    352359, 355669, 352325, 356387, 358644, 
    354369, 354423, 355726, 356210, 356291, 
    356651, 352567, 354491, 354747, 354815, 
    355792, 356220, 352322, 354453, 354878, 
    356601, 356691, 358593, 351406, 352458
  ) 
  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.00165

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "280.31"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "usergroup",
        "used_key_parts": [
          "product_id",
          "usergroup_id",
          "lower_limit"
        ],
        "key_length": "9",
        "rows_examined_per_scan": 200,
        "rows_produced_per_join": 200,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "240.31",
          "eval_cost": "40.00",
          "prefix_cost": "280.31",
          "data_read_per_join": "12K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`webmarco`.`cscart_product_prices`.`lower_limit` = 1) and (`webmarco`.`cscart_product_prices`.`product_id` in (352386,358695,351371,354344,355656,355717,355719,357488,352231,354808,356679,357144,357567,358789,351261,355919,357569,359418,352382,351602,355645,355142,358320,351445,354347,355723,357136,351377,355661,357611,358359,358477,358540,358877,352477,354416,356003,356241,356526,358730,352616,352754,356383,356426,355938,357139,358395,358769,349759,351301,351349,351818,352701,358389,358438,358544,356680,357524,349747,352680,352327,355856,356432,357525,358384,351405,351413,351546,352588,354456,354837,355026,355918,356619,350179,352359,355669,352325,356387,358644,354369,354423,355726,356210,356291,356651,352567,354491,354747,354815,355792,356220,352322,354453,354878,356601,356691,358593,351406,352458)) and (`webmarco`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}