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 (
    10267, 10266, 10265, 10264, 10263, 12570, 
    12567, 12569, 12568, 10261, 10260, 
    10259, 10258, 10257, 10256, 10255, 
    10254, 10253, 10252, 10251, 10250, 
    10249, 10248, 10247, 10246, 10245, 
    10244, 10243, 10242, 10241, 10203, 
    10204, 10205, 10206, 10207, 10208, 
    10209, 10210, 10211, 10212, 10213, 
    10214, 10215, 10216, 10217, 10218, 
    10219, 10220, 10221, 10222, 10144, 
    10145, 10146, 10147, 10148, 10149, 
    10150, 10151, 10152, 12969, 10103, 
    10104, 10105, 10106
  ) 
  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.00080

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": 64,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (10267,10266,10265,10264,10263,12570,12567,12569,12568,10261,10260,10259,10258,10257,10256,10255,10254,10253,10252,10251,10250,10249,10248,10247,10246,10245,10244,10243,10242,10241,10203,10204,10205,10206,10207,10208,10209,10210,10211,10212,10213,10214,10215,10216,10217,10218,10219,10220,10221,10222,10144,10145,10146,10147,10148,10149,10150,10151,10152,12969,10103,10104,10105,10106)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10103 4702.31000000
10104 4702.31000000
10105 4702.31000000
10106 4702.31000000
10144 1606.00000000
10145 1606.00000000
10146 1606.00000000
10147 1606.00000000
10148 1606.00000000
10149 1606.00000000
10150 1606.00000000
10151 1606.00000000
10152 1606.00000000
10203 1894.30000000
10204 1894.30000000
10205 1894.30000000
10206 1894.30000000
10207 1894.30000000
10208 1894.30000000
10209 1894.30000000
10210 1894.30000000
10211 1894.30000000
10212 1894.30000000
10213 1894.30000000
10214 1894.30000000
10215 1894.30000000
10216 1894.30000000
10217 1894.30000000
10218 1894.30000000
10219 1894.30000000
10220 1894.30000000
10221 1894.30000000
10222 1894.30000000
10241 1136.58000000
10242 1136.58000000
10243 1136.58000000
10244 1136.58000000
10245 1136.58000000
10246 1136.58000000
10247 1136.58000000
10248 1136.58000000
10249 1136.58000000
10250 1136.58000000
10251 1136.58000000
10252 1136.58000000
10253 1136.58000000
10254 1136.58000000
10255 1136.58000000
10256 1136.58000000
10257 1136.58000000
10258 1136.58000000
10259 1136.58000000
10260 1136.58000000
10261 1136.58000000
10263 2150.58000000
10264 2150.58000000
10265 2150.58000000
10266 2150.58000000
10267 2150.58000000
12567 10552.12000000
12568 10552.12000000
12569 10552.12000000
12570 11031.68000000
12969 1606.00000000