[Spce-user] Daily fraud limit calculation is too painful

[Walter Klomp]

Hi Guys,

Hope to find some SQL guru’s here…

the daily fraud checking script gets executed every half an hour on the SQL server (and is busy there for about 10 minutes (on 45000+ subscribers). On my system I have changed the interval to 15 minutes as we had instances of customers being compromised and this way it would capture it sooner.  Ideally we should optimise it to check for each CDR written just for that 1 customer (that would probably be faster).

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;


There are 2 issues here.

1. daily fraud limit should be calculated over the last 24 hours, so I propose we do:

WHERE cdr.start_time BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), “INTERVAL 1 DAY"))
                         AND UNIX_TIMESTAMP(NOW())

2. How can we speed up the calculation to only take into account the subscribers that have made any calls since the last time it was checked. (i.e. last half hour) - that should drastically reduce the number of rows to be verified as well.

What do you all think ?

Walter.