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, 461219, 153, 
    11893, 312414, 390978, 461036, 461212, 
    461189, 116547, 391030, 391037, 11677, 
    391029, 22384, 21914, 312847, 461190, 
    168, 164, 312455, 312443, 461027, 312444, 
    312436, 312456, 461033, 461035, 281, 
    391040, 312450, 312424, 432167, 312870, 
    312412, 165, 390993, 175, 312426, 116529, 
    12708, 393787, 400913, 221, 116539, 
    312425, 312043, 391032, 153469, 183, 
    116, 312418, 312451, 312421, 312882, 
    312411, 230, 461039, 119, 173, 391036, 
    280, 12695, 312044, 312419, 400923, 
    312449, 434190, 12966, 391035, 460948, 
    391034, 391033, 227, 12702, 116425, 
    457770, 400971
  ) 
  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.00145

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,461219,153,11893,312414,390978,461036,461212,461189,116547,391030,391037,11677,391029,22384,21914,312847,461190,168,164,312455,312443,461027,312444,312436,312456,461033,461035,281,391040,312450,312424,432167,312870,312412,165,390993,175,312426,116529,12708,393787,400913,221,116539,312425,312043,391032,153469,183,116,312418,312451,312421,312882,312411,230,461039,119,173,391036,280,12695,312044,312419,400923,312449,434190,12966,391035,460948,391034,391033,227,12702,116425,457770,400971) 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"
        }
      }
    ]
  }
}