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 (
    3855, 3859, 5105, 8812, 6163, 6853, 6161, 
    7807, 6061, 6062, 6854, 6060, 7823, 
    7838, 7827, 6821, 7829, 7811, 7866, 
    10103, 6852, 7841, 6850, 6855, 7861, 
    7856, 10160, 7863, 7840, 7812, 7855, 
    7860, 7868, 7869, 7870, 10242, 7813, 
    7825, 8242, 8373
  ) 
  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.00220

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "29.36"
    },
    "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": 43,
        "rows_produced_per_join": 8,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (3855,3859,5105,8812,6163,6853,6161,7807,6061,6062,6854,6060,7823,7838,7827,6821,7829,7811,7866,10103,6852,7841,6850,6855,7861,7856,10160,7863,7840,7812,7855,7860,7868,7869,7870,10242,7813,7825,8242,8373))",
        "cost_info": {
          "read_cost": "28.50",
          "eval_cost": "0.86",
          "prefix_cost": "29.36",
          "data_read_per_join": "206"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`lower_limit` = 1) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
3855 20000.00000000
3859 20000.00000000
5105 36750.00000000
6060 141000.00000000
6061 103000.00000000
6062 177000.00000000
6161 19000.00000000
6163 72000.00000000
6821 18000.00000000
6850 18500.00000000
6852 22500.00000000
6853 19500.00000000
6854 15500.00000000
6855 15500.00000000
7807 2750.00000000
7811 2500.00000000
7812 2250.00000000
7813 2250.00000000
7823 2500.00000000
7825 3750.00000000
7827 2250.00000000
7829 3750.00000000
7838 1750.00000000
7840 2250.00000000
7841 2750.00000000
7855 3250.00000000
7856 2250.00000000
7860 9750.00000000
7861 3250.00000000
7863 2000.00000000
7866 1500.00000000
7868 2500.00000000
7869 2750.00000000
7870 2750.00000000
8242 32000.00000000
8373 14000.00000000
8812 38000.00000000
10103 6000.00000000
10160 9000.00000000
10242 10000.00000000