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 (
    37513, 
    37514, 
    37515, 
    37516, 
    37520, 
    37521, 
    37522, 
    37523, 
    37524, 
    37525, 
    37526, 
    37527, 
    37528, 
    37529, 
    37530, 
    37531, 
    37532, 
    37533, 
    37534, 
    37535, 
    37536, 
    37537, 
    37538, 
    37539, 
    37540, 
    37541, 
    37542, 
    37543, 
    37544, 
    37545, 
    37546, 
    37547, 
    37548, 
    37549, 
    37550, 
    37551, 
    37552, 
    37553, 
    37554, 
    37555, 
    37557, 
    37558, 
    37559, 
    37560, 
    37561, 
    37562, 
    37563, 
    37820, 
    37821, 
    37822, 
    37823, 
    37824, 
    37825, 
    37826, 
    37827, 
    37828, 
    37829, 
    37830, 
    37831, 
    37832, 
    37833, 
    37834, 
    37835, 
    37836, 
    37837, 
    37838, 
    37839, 
    37840, 
    37841, 
    37842, 
    37843, 
    37844, 
    37845, 
    37846, 
    37847, 
    37848, 
    37849, 
    37850, 
    37851, 
    37852, 
    37853, 
    37854, 
    37855, 
    37856, 
    37858, 
    37859, 
    37860, 
    37861, 
    37862, 
    37863, 
    37864, 
    37865, 
    37866, 
    37867, 
    37868, 
    37869, 
    37870, 
    37871, 
    37872, 
    37873, 
    37874, 
    37875, 
    37877, 
    37878, 
    37879, 
    37880, 
    37881, 
    37882, 
    37883, 
    37884, 
    37885, 
    37886, 
    37887, 
    37888, 
    37889, 
    37890, 
    37891, 
    37892, 
    37893, 
    37894, 
    37895, 
    37896, 
    37897, 
    38150, 
    38151, 
    38152, 
    38155, 
    38156
  ) 
  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.00150

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "419.21"
    },
    "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": 208,
        "rows_produced_per_join": 41,
        "filtered": "19.99",
        "index_condition": "(`portal`.`q_product_prices`.`product_id` in (37513,37514,37515,37516,37520,37521,37522,37523,37524,37525,37526,37527,37528,37529,37530,37531,37532,37533,37534,37535,37536,37537,37538,37539,37540,37541,37542,37543,37544,37545,37546,37547,37548,37549,37550,37551,37552,37553,37554,37555,37557,37558,37559,37560,37561,37562,37563,37820,37821,37822,37823,37824,37825,37826,37827,37828,37829,37830,37831,37832,37833,37834,37835,37836,37837,37838,37839,37840,37841,37842,37843,37844,37845,37846,37847,37848,37849,37850,37851,37852,37853,37854,37855,37856,37858,37859,37860,37861,37862,37863,37864,37865,37866,37867,37868,37869,37870,37871,37872,37873,37874,37875,37877,37878,37879,37880,37881,37882,37883,37884,37885,37886,37887,37888,37889,37890,37891,37892,37893,37894,37895,37896,37897,38150,38151,38152,38155,38156))",
        "cost_info": {
          "read_cost": "410.89",
          "eval_cost": "8.32",
          "prefix_cost": "419.21",
          "data_read_per_join": "998"
        },
        "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
37513	99.000000
37514	109.000000
37515	109.000000
37516	79.000000
37520	109.000000
37521	279.000000
37522	79.000000
37523	79.000000
37524	79.000000
37525	79.000000
37526	109.000000
37527	359.000000
37528	129.000000
37529	169.000000
37530	119.000000
37531	79.000000
37532	1339.000000
37533	109.000000
37534	99.000000
37535	189.000000
37536	129.000000
37537	119.000000
37538	259.000000
37539	119.000000
37540	119.000000
37541	169.000000
37542	239.000000
37543	249.000000
37544	309.000000
37545	209.000000
37546	179.000000
37547	179.000000
37548	139.000000
37549	219.000000
37550	189.000000
37551	189.000000
37552	219.000000
37553	249.000000
37554	189.000000
37555	129.000000
37557	229.000000
37558	139.000000
37559	179.000000
37560	199.000000
37561	219.000000
37562	209.000000
37563	169.000000
37820	29.000000
37821	29.000000
37822	29.000000
37823	29.000000
37824	29.000000
37825	29.000000
37826	29.000000
37827	29.000000
37828	19.000000
37829	149.000000
37830	69.000000
37831	99.000000
37832	19.000000
37833	339.000000
37834	49.000000
37835	49.000000
37836	49.000000
37837	49.000000
37838	39.000000
37839	69.000000
37840	69.000000
37841	49.000000
37842	19.000000
37843	29.000000
37844	49.000000
37845	29.000000
37846	29.000000
37847	29.000000
37848	49.000000
37849	59.000000
37850	79.000000
37851	69.000000
37852	89.000000
37853	69.000000
37854	139.000000
37855	129.000000
37856	129.000000
37858	39.000000
37859	69.000000
37860	69.000000
37861	49.000000
37862	19.000000
37863	49.000000
37864	19.000000
37865	19.000000
37866	19.000000
37867	199.000000
37868	199.000000
37869	199.000000
37870	79.000000
37871	69.000000
37872	169.000000
37873	59.000000
37874	129.000000
37875	19.000000
37877	19.000000
37878	69.000000
37879	19.000000
37880	69.000000
37881	79.000000
37882	69.000000
37883	59.000000
37884	79.000000
37885	79.000000
37886	49.000000
37887	39.000000
37888	49.000000
37889	49.000000
37890	69.000000
37891	59.000000
37892	39.000000
37893	29.000000
37894	39.000000
37895	39.000000
37896	19.000000
37897	89.000000
38150	9.000000
38151	9.000000
38152	9.000000
38155	9.000000
38156	9.000000