[Spce-user] This query "kills" my server every half an hour..

[Walter Klomp]

Hi,

# Time: 160627 16:34:30
# User at Host: soap[soap] @ localhost []
# Query_time: 268.000337  Lock_time: 0.000220 Rows_sent: 0  Rows_examined: 133888
SET timestamp=1467016470;
SELECT bpinfo.type, bpinfo.id,
       SUM(cdr.source_customer_cost) as daily_cost,
       bpinfo.fraud_daily_limit, bpinfo.fraud_daily_lock, bpinfo.fraud_daily_notify
FROM (
  SELECT contracts.id,
    CASE WHEN cfp.fraud_daily_limit > 0 THEN 'account_limit'
                                        ELSE 'profile_limit' END as type,
    IF (cfp.fraud_daily_limit > 0, cfp.fraud_daily_limit, bp.fraud_daily_limit) as fraud_daily_limit,
    IF (cfp.fraud_daily_limit > 0, cfp.fraud_daily_lock, bp.fraud_daily_lock) as fraud_daily_lock,
    IF (cfp.fraud_daily_limit > 0, cfp.fraud_daily_notify, bp.fraud_daily_notify) as fraud_daily_notify
  FROM billing.contracts
  JOIN billing.billing_profiles bp
    ON (bp.id =
      (SELECT m.billing_profile_id
        FROM billing.billing_mappings m
         USE INDEX (contractid_idx)
        WHERE ((m.start_date IS NULL) OR (m.start_date <= NOW()))
          AND ((m.end_date IS NULL) OR (m.end_date >= NOW()))
          AND (m.contract_id = contracts.id)
        ORDER BY m.start_date DESC LIMIT 1
      )
    )
  LEFT JOIN billing.contract_fraud_preferences cfp ON cfp.contract_id = contracts.id
  WHERE (contracts.status = 'active')
    AND (cfp.fraud_daily_limit > 0 OR bp.fraud_daily_limit > 0)
) as bpinfo
JOIN accounting.cdr ON cdr.source_account_id = bpinfo.id
WHERE cdr.start_time BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'))
                         AND UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59'))
GROUP BY bpinfo.id
HAVING daily_cost > fraud_daily_limit;


I understand this is part of the fraud check every half hour.  How can I run this on my SQL slave while running everything else on the master (localhost) ?
Is there an easy patch for this?  running 3.8.5 -  I can’t upgrade to the latest Sipwise yet as my interface is reliant on SOAP (yes, I know, we’ll eventually move).

Or is there something I can do to optimise this query to go faster?

Thanks
Walter.