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 (
    1230, 1229, 1228, 1227, 1226, 1225, 1224, 
    1223, 1222, 1221, 1220, 1219, 1218, 
    1217, 1216, 1215, 1170, 1171, 1177, 
    1175, 1168, 1183, 1184, 1174, 1187, 
    67, 1191, 1176, 1167, 1169, 1194, 1179, 
    1180, 1181, 1197, 1182, 1201, 1202, 
    1198, 1178, 603, 1185, 1209, 1186, 1210, 
    1211, 1203, 1212
  ) 
  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.00178

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": 48,
      "filtered": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (1230,1229,1228,1227,1226,1225,1224,1223,1222,1221,1220,1219,1218,1217,1216,1215,1170,1171,1177,1175,1168,1183,1184,1174,1187,67,1191,1176,1167,1169,1194,1179,1180,1181,1197,1182,1201,1202,1198,1178,603,1185,1209,1186,1210,1211,1203,1212)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
67 48000.00000000
603 0.00000000
1167 48000.00000000
1168 32000.00000000
1169 170000.00000000
1170 29000.00000000
1171 88000.00000000
1174 91000.00000000
1175 70200.00000000
1176 83000.00000000
1177 48000.00000000
1178 60000.00000000
1179 260000.00000000
1180 24000.00000000
1181 34000.00000000
1182 65000.00000000
1183 38000.00000000
1184 70000.00000000
1185 26000.00000000
1186 21000.00000000
1187 47000.00000000
1191 83000.00000000
1194 42000.00000000
1197 58000.00000000
1198 30000.00000000
1201 65000.00000000
1202 46000.00000000
1203 12500.00000000
1209 21000.00000000
1210 12500.00000000
1211 40000.00000000
1212 40000.00000000
1215 0.00000000
1216 88000.00000000
1217 122000.00000000
1218 335000.00000000
1219 56000.00000000
1220 56000.00000000
1221 195000.00000000
1222 54000.00000000
1223 23000.00000000
1224 54000.00000000
1225 82300.00000000
1226 82300.00000000
1227 125000.00000000
1228 63000.00000000
1229 50000.00000000
1230 45000.00000000