SELECT 
  p.product_id, 
  p.list_price, 
  pp.price, 
  ROUND(
    (p.list_price - pp.price) / p.list_price * 100
  ) AS discount_pct 
FROM 
  cscart_products p 
  INNER JOIN cscart_product_prices pp ON pp.product_id = p.product_id 
  AND pp.lower_limit = 1 
WHERE 
  p.product_id IN (
    461025, 461181, 111833, 312414, 461219, 
    390978, 11893, 461036, 312412, 153, 
    312456, 230, 312444, 461212, 391037, 
    312436, 312847, 168, 391029, 164, 12708, 
    233, 312443, 227, 461189, 390993, 116547, 
    170, 312455, 391030, 312424, 22384, 
    21914, 165, 461033, 312451, 11677, 
    391040, 221, 116427, 461190, 393787, 
    312870, 312418, 312446, 183, 461035, 
    461027, 312421, 312449, 166, 116539, 
    312864, 391025, 312450, 400913, 12695, 
    461034, 280, 432167, 400914, 281, 312863, 
    312458, 175, 312724, 116425, 312723, 
    116529, 312411, 12966, 202, 312882, 
    119, 312051, 400915, 153469, 312426, 
    231, 400924
  ) 
  AND p.status = 'A' 
  AND p.list_price > 0 
  AND p.list_price > pp.price 
  AND ROUND(
    (p.list_price - pp.price) / p.list_price * 100
  ) >= 5

Query time 0.00135

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.28259592,
    "nested_loop": [
      {
        "table": {
          "table_name": "p",
          "access_type": "range",
          "possible_keys": ["PRIMARY", "status"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "loops": 1,
          "rows": 80,
          "cost": 0.13989432,
          "filtered": 100,
          "attached_condition": "p.product_id in (461025,461181,111833,312414,461219,390978,11893,461036,312412,153,312456,230,312444,461212,391037,312436,312847,168,391029,164,12708,233,312443,227,461189,390993,116547,170,312455,391030,312424,22384,21914,165,461033,312451,11677,391040,221,116427,461190,393787,312870,312418,312446,183,461035,461027,312421,312449,166,116539,312864,391025,312450,400913,12695,461034,280,432167,400914,281,312863,312458,175,312724,116425,312723,116529,312411,12966,202,312882,119,312051,400915,153469,312426,231,400924) and p.`status` = 'A' and p.list_price > 0"
        }
      },
      {
        "table": {
          "table_name": "pp",
          "access_type": "ref",
          "possible_keys": ["usergroup", "product_id", "lower_limit"],
          "key": "usergroup",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["u985510652_ecartify.p.product_id"],
          "loops": 80,
          "rows": 1,
          "cost": 0.1427016,
          "filtered": 100,
          "attached_condition": "pp.lower_limit = 1 and p.list_price > pp.price and round((p.list_price - pp.price) / p.list_price * 100,0) >= 5"
        }
      }
    ]
  }
}