Sandbox

SELECT 
  q_product_prices.product_id, 
  MIN(
    IF(
      q_product_prices.percentage_discount = 0, 
      q_product_prices.price, 
      q_product_prices.price - (
        q_product_prices.price * q_product_prices.percentage_discount
      )/ 100
    )
  ) AS price 
FROM 
  q_product_prices 
WHERE 
  q_product_prices.product_id IN (
    47678, 
    47679, 
    47680, 
    47681, 
    47682, 
    47683, 
    47684, 
    47685, 
    47686, 
    47688, 
    47690, 
    47691, 
    47692, 
    47693, 
    47694, 
    47695, 
    47696, 
    47697, 
    47698, 
    47699, 
    47700, 
    47701, 
    47702, 
    47703, 
    47705, 
    47706, 
    47707, 
    47708, 
    47709, 
    47710, 
    47711, 
    47712, 
    47714, 
    47719, 
    47720, 
    47721, 
    47722, 
    47725, 
    47730, 
    47731, 
    47732, 
    47733, 
    47734, 
    47735, 
    47736, 
    47737, 
    47738, 
    47739, 
    47740, 
    47741, 
    47742, 
    47743, 
    47744, 
    47745, 
    47746, 
    47747, 
    47748, 
    47749, 
    47750, 
    47751, 
    47752, 
    47753, 
    47754, 
    47755, 
    47756, 
    47757, 
    47758, 
    47760, 
    47761, 
    47762, 
    47763, 
    47764, 
    47765, 
    47766, 
    47767, 
    47768, 
    47769, 
    47770, 
    47771, 
    47772, 
    47773, 
    47774, 
    45784, 
    45785, 
    45786, 
    45787, 
    45788, 
    45789, 
    45790, 
    45791, 
    45792, 
    45793, 
    45794, 
    45795, 
    45796, 
    45797, 
    45798, 
    45799, 
    45800, 
    45801, 
    45802, 
    45803, 
    45804, 
    45805, 
    45806, 
    45807, 
    45808, 
    45809, 
    45810, 
    45811, 
    45812, 
    45813, 
    45814, 
    45815, 
    45816, 
    45817, 
    45818, 
    45819, 
    45820, 
    45821, 
    45822, 
    45823, 
    45824, 
    45825, 
    45826, 
    45827, 
    45828, 
    45829
  ) 
  AND q_product_prices.lower_limit = 1 
  AND q_product_prices.usergroup_id IN (0, 1) 
GROUP BY 
  q_product_prices.product_id

Query time 0.00156

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "308.61"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "q_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": 129,
        "rows_produced_per_join": 25,
        "filtered": "19.99",
        "index_condition": "(`portal`.`q_product_prices`.`product_id` in (47678,47679,47680,47681,47682,47683,47684,47685,47686,47688,47690,47691,47692,47693,47694,47695,47696,47697,47698,47699,47700,47701,47702,47703,47705,47706,47707,47708,47709,47710,47711,47712,47714,47719,47720,47721,47722,47725,47730,47731,47732,47733,47734,47735,47736,47737,47738,47739,47740,47741,47742,47743,47744,47745,47746,47747,47748,47749,47750,47751,47752,47753,47754,47755,47756,47757,47758,47760,47761,47762,47763,47764,47765,47766,47767,47768,47769,47770,47771,47772,47773,47774,45784,45785,45786,45787,45788,45789,45790,45791,45792,45793,45794,45795,45796,45797,45798,45799,45800,45801,45802,45803,45804,45805,45806,45807,45808,45809,45810,45811,45812,45813,45814,45815,45816,45817,45818,45819,45820,45821,45822,45823,45824,45825,45826,45827,45828,45829))",
        "cost_info": {
          "read_cost": "303.45",
          "eval_cost": "5.16",
          "prefix_cost": "308.61",
          "data_read_per_join": "618"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`portal`.`q_product_prices`.`lower_limit` = 1) and (`portal`.`q_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id	price
45784	29.000000
45785	134.000000
45786	194.000000
45787	79.000000
45788	84.000000
45789	399.000000
45790	119.000000
45791	149.000000
45792	101.000000
45793	111.000000
45794	626.000000
45795	261.000000
45796	499.000000
45797	646.000000
45798	84.000000
45799	129.000000
45800	129.000000
45801	129.000000
45802	129.000000
45803	206.000000
45804	206.000000
45805	46.000000
45806	79.000000
45807	184.000000
45808	114.000000
45809	56.000000
45810	89.000000
45811	236.000000
45812	89.000000
45813	59.000000
45814	106.000000
45815	59.000000
45816	49.000000
45817	62.000000
45818	71.000000
45819	84.000000
45820	39.000000
45821	22.000000
45822	201.000000
45823	116.000000
45824	226.000000
45825	62.000000
45826	26.000000
45827	111.000000
45828	116.000000
45829	1316.000000
47678	36.000000
47679	119.000000
47680	71.000000
47681	64.000000
47682	39.000000
47683	119.000000
47684	56.000000
47685	154.000000
47686	26.000000
47688	74.000000
47690	61.000000
47691	39.000000
47692	31.000000
47693	109.000000
47694	86.000000
47695	164.000000
47696	101.000000
47697	141.000000
47698	81.000000
47699	214.000000
47700	49.000000
47701	74.000000
47702	84.000000
47703	84.000000
47705	64.000000
47706	92.000000
47707	121.000000
47708	131.000000
47709	119.000000
47710	141.000000
47711	154.000000
47712	149.000000
47714	156.000000
47719	189.000000
47720	161.000000
47721	111.000000
47722	1462.000000
47725	1131.000000
47730	71.000000
47731	91.000000
47732	119.000000
47733	109.000000
47734	129.000000
47735	139.000000
47736	104.000000
47737	124.000000
47738	111.000000
47739	141.000000
47740	99.000000
47741	119.000000
47742	126.000000
47743	134.000000
47744	119.000000
47745	154.000000
47746	26.000000
47747	46.000000
47748	74.000000
47749	799.000000
47750	64.000000
47751	24.000000
47752	81.000000
47753	11.000000
47754	16.000000
47755	21.000000
47756	24.000000
47757	9.000000
47758	6.000000
47760	19.000000
47761	32.000000
47762	29.000000
47763	51.000000
47764	69.000000
47765	22.000000
47766	4.000000
47767	74.000000
47768	92.000000
47769	11.000000
47770	9.000000
47771	71.000000
47772	39.000000
47773	9.000000
47774	62.000000