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 (
    6730, 7172, 6924, 7084, 6814, 6683, 7411, 
    6684, 6685, 6686, 6687, 6688, 6689, 
    6690, 6693, 11052, 6694, 6912, 6691, 
    6913, 6682, 6695, 6696, 11104, 6692, 
    8688, 6697, 8000, 6698, 6699, 6700, 
    6701, 6702, 6703, 6704, 6705, 6707, 
    6708, 6709, 6710, 6714, 6706, 6716, 
    6717, 6718, 6715, 6719, 6720, 6721, 
    6711, 6723, 6722, 6724, 6712, 6725, 
    6726, 6727, 6728, 6729, 6731, 6732, 
    8742, 6733, 6734, 6735, 6736, 6713, 
    6737, 6738, 6739, 6740, 6741, 6742, 
    6748, 8801, 8802, 11103, 6744, 6745, 
    6746, 6747, 10992, 6749, 6750, 8743, 
    6751, 6752, 6753, 6754, 6755, 6756, 
    6757, 6758, 6759, 6760, 6915, 6916, 
    6761, 6762, 11378
  ) 
  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.01286

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "cscart_product_prices.product_id",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "cscart_product_prices",
              "access_type": "ref",
              "possible_keys": [
                "usergroup",
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "key": "lower_limit",
              "key_length": "3",
              "used_key_parts": ["lower_limit"],
              "ref": ["const"],
              "rows": 11706,
              "filtered": 100,
              "attached_condition": "cscart_product_prices.lower_limit <=> 1 and cscart_product_prices.product_id in (6730,7172,6924,7084,6814,6683,7411,6684,6685,6686,6687,6688,6689,6690,6693,11052,6694,6912,6691,6913,6682,6695,6696,11104,6692,8688,6697,8000,6698,6699,6700,6701,6702,6703,6704,6705,6707,6708,6709,6710,6714,6706,6716,6717,6718,6715,6719,6720,6721,6711,6723,6722,6724,6712,6725,6726,6727,6728,6729,6731,6732,8742,6733,6734,6735,6736,6713,6737,6738,6739,6740,6741,6742,6748,8801,8802,11103,6744,6745,6746,6747,10992,6749,6750,8743,6751,6752,6753,6754,6755,6756,6757,6758,6759,6760,6915,6916,6761,6762,11378) and cscart_product_prices.usergroup_id in (0,1)"
            }
          }
        ]
      }
    }
  }
}