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 (
    10309, 
    10310, 
    10311, 
    10312, 
    10313, 
    10314, 
    10315, 
    10316, 
    10317, 
    10318, 
    10319, 
    10322, 
    10324, 
    10325, 
    10326, 
    1471, 
    1472, 
    1473, 
    1474, 
    1475, 
    1476, 
    1477, 
    1478, 
    1479, 
    1480, 
    1481, 
    1482, 
    1483, 
    1484, 
    1485, 
    1486, 
    1487, 
    1488, 
    1489, 
    1490, 
    1491, 
    1492, 
    1493, 
    1494, 
    1495, 
    1496, 
    1497, 
    1498, 
    1499, 
    1500, 
    1502, 
    1503, 
    1504, 
    1505, 
    1506, 
    1507, 
    1508, 
    1509, 
    1510, 
    1511, 
    1512, 
    1513, 
    1514, 
    1515, 
    1516, 
    1517, 
    1518, 
    1521, 
    1522, 
    1523, 
    1528, 
    1529, 
    1530, 
    1531, 
    1532, 
    1533, 
    1534, 
    1535, 
    1536, 
    1537, 
    1538, 
    1539, 
    1540, 
    1541, 
    1542, 
    1543, 
    1544, 
    1545, 
    1546, 
    1547, 
    1548, 
    1549, 
    1550, 
    1551, 
    1552, 
    1553, 
    1554, 
    1555, 
    1556, 
    1557, 
    1558, 
    1559, 
    1560, 
    1561, 
    1562, 
    1563, 
    1564, 
    1565, 
    1566, 
    1567, 
    1568, 
    1569, 
    1570, 
    1571, 
    1572, 
    1573, 
    1574, 
    1577, 
    1578, 
    1579, 
    1580, 
    1581, 
    1582, 
    1583, 
    1584, 
    1585, 
    1586, 
    1587, 
    1588, 
    1589, 
    1590, 
    1591, 
    1592
  ) 
  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.00159

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 (10309,10310,10311,10312,10313,10314,10315,10316,10317,10318,10319,10322,10324,10325,10326,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1485,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1502,1503,1504,1505,1506,1507,1508,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1521,1522,1523,1528,1529,1530,1531,1532,1533,1534,1535,1536,1537,1538,1539,1540,1541,1542,1543,1544,1545,1546,1547,1548,1549,1550,1551,1552,1553,1554,1555,1556,1557,1558,1559,1560,1561,1562,1563,1564,1565,1566,1567,1568,1569,1570,1571,1572,1573,1574,1577,1578,1579,1580,1581,1582,1583,1584,1585,1586,1587,1588,1589,1590,1591,1592))",
        "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
1471	19.000000
1472	19.000000
1473	19.000000
1474	19.000000
1475	19.000000
1476	19.000000
1477	19.000000
1478	19.000000
1479	29.000000
1480	29.000000
1481	29.000000
1482	29.000000
1483	29.000000
1484	29.000000
1485	29.000000
1486	29.000000
1487	29.000000
1488	29.000000
1489	14.000000
1490	14.000000
1491	14.000000
1492	14.000000
1493	14.000000
1494	14.000000
1495	14.000000
1496	14.000000
1497	14.000000
1498	20.000000
1499	20.000000
1500	20.000000
1502	20.000000
1503	29.000000
1504	29.000000
1505	29.000000
1506	29.000000
1507	29.000000
1508	29.000000
1509	29.000000
1510	29.000000
1511	29.000000
1512	29.000000
1513	29.000000
1514	29.000000
1515	29.000000
1516	29.000000
1517	29.000000
1518	29.000000
1521	25.000000
1522	25.000000
1523	25.000000
1528	12.000000
1529	12.000000
1530	12.000000
1531	12.000000
1532	12.000000
1533	12.000000
1534	6.000000
1535	6.000000
1536	9.000000
1537	9.000000
1538	9.000000
1539	9.000000
1540	9.000000
1541	9.000000
1542	9.000000
1543	9.000000
1544	9.000000
1545	9.000000
1546	9.000000
1547	9.000000
1548	29.000000
1549	29.000000
1550	29.000000
1551	29.000000
1552	29.000000
1553	29.000000
1554	29.000000
1555	29.000000
1556	29.000000
1557	29.000000
1558	29.000000
1559	29.000000
1560	29.000000
1561	29.000000
1562	19.000000
1563	19.000000
1564	19.000000
1565	19.000000
1566	19.000000
1567	19.000000
1568	25.000000
1569	25.000000
1570	25.000000
1571	25.000000
1572	25.000000
1573	25.000000
1574	25.000000
1577	25.000000
1578	25.000000
1579	25.000000
1580	25.000000
1581	25.000000
1582	26.000000
1583	26.000000
1584	26.000000
1585	26.000000
1586	29.000000
1587	29.000000
1588	29.000000
1589	29.000000
1590	29.000000
1591	29.000000
1592	25.000000
10309	99.000000
10310	99.000000
10311	85.000000
10312	109.000000
10313	85.000000
10314	79.000000
10315	109.000000
10316	85.000000
10317	19.000000
10318	69.000000
10319	99.000000
10322	119.000000
10324	139.000000
10325	119.000000
10326	69.000000