[Spce-user] Fraud calculation takes hours
Alejandro Grijalba
agrijalba at innovasur.es
Thu Jan 25 10:16:02 EST 2018
Hi, this is the command and the output (i cannot understand it much):
mysql> EXPLAIN SELECT bpinfo.id, bpinfo.reseller_id, bpinfo.interval,
bpinfo.type, IF (bpinfo.fraud_use_reseller_rates > 0,
SUM(cdr.source_reseller_cost), SUM(cdr.source_customer_cost)) as
interval_cost, bpinfo.interval_limit,
bpinfo.interval_lock, bpinf o.interval_notify,
bpinfo.fraud_use_reseller_rates as use_reseller_rates FROM ( SELECT
c.id, n.reseller_id, bp.fraud_use_reseller_rates, i.interval, IF
(i.interval = 'month', IF (cfp.fraud_interval_limit > 0,
'account_limit', 'profile_limit'), IF (cfp.fraud_daily_limit >
0, 'a ccount_limit', 'profile_limit') ) AS type,
IF (i.interval = 'month', IF (cfp.fraud_interval_limit > 0,
cfp.fraud_interval_limit, bp.fraud_interval_limit), IF
(cfp.fraud_daily_limit > 0, cfp.fraud_daily_limit,
bp.fraud_daily_limit) ) AS interval_limit, IF (i.interval =
'month', IF (cfp.fraud_interval_limit > 0,
cfp.fraud_interval_lock, bp.fraud_interval_lock), IF
(cfp.fraud_daily_limit > 0, cfp.fraud_daily_lock,
bp.fraud_daily_lock) ) AS interval_lock, IF (i.interval =
'month', IF (cfp.fraud_interval_limit > 0,
cfp.fraud_i nterval_notify, bp.fraud_interval_notify), IF
(cfp.fraud_daily_limit > 0, cfp.fraud_daily_notify,
bp.fraud_daily_notify) ) AS interval_notify FROM (SELECT
IF('day' = 'month','month','day') AS 'interval') i, billing.contracts
c INNER JOIN ( SELECT bm1.contract_id,MAX(bm1.id) AS id
FROM billing.billing_mappings bm1 INNER JOIN (
SELECT bm2.contract_id, MAX(bm2.start_date) AS start_date
FROM billing.billing_mappings bm2 WHERE (bm2.end_date >=
NOW() OR bm2.end_date IS NULL) AND (bm2.start_date
<= NOW() OR bm2.start_date IS NULL)
GROUP BY bm2.contract_id ) AS max ON bm1.contract_id =
max.contract_id AND bm1.start_date <=> max.start_date GROUP BY
bm1.contract_id ) AS bm_actual ON bm_actual.contract_id = c.id JOIN
billing.billing_mappings bm ON bm.id = bm_actual.id JOIN
billing.billing_profiles bp ON bp.id = bm.billing_profile_id
JOIN billing.contacts n ON n.id = c.contact_id JOIN
billing.resellers r ON r.id = n.reseller_id LEFT JOIN
billing.contract_fraud_preferences cfp ON cfp.contract_id = c.id WHERE
c.status = 'active' HAVING interval_limit > 0 ) AS bpinfo JOIN
accounting.cdr ON cdr.source_account_id = bpinfo.id WHERE CASE WHEN
bpinfo.inter val = 'month' THEN cdr.start_time BETWEEN
UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00')) AND
UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01
00:00:00'))-1 ELSE cdr.start_time BETWEEN
UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')) AND
UNIX_TIMESTAMP(DATE_FORMAT(NOW() +
INTERVAL 1 DAY, '%Y-%m-%d 00:00:00'))-1 END GROUP BY bpinfo.id HAVING
interval_cost >= interval_limit;
+----+-------------+------------+--------+--------------------------------------------+-------------------+---------+-------------------------------+------+---------------------------------+
| id | select_type | table | type |
possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+------------+--------+--------------------------------------------+-------------------+---------+-------------------------------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL |
NULL | NULL | NULL
| NULL | 6580 | Using temporary; Using filesort |
| 1 | PRIMARY | cdr | ref |
said_stime_idx | said_stime_idx | 4
| bpinfo.id | 154 | Using where |
| 2 | DERIVED | <derived3> | system |
NULL | NULL | NULL
| NULL | 1 | |
| 2 | DERIVED | r | index |
PRIMARY | contractid_idx | 4
| NULL | 1 | Using index |
| 2 | DERIVED | n | ref |
PRIMARY,ct_resellerid_ref | ct_resellerid_ref | 5
| billing.r.id | 2 | Using where; Using index |
| 2 | DERIVED | <derived4> | ALL |
NULL | NULL | NULL
| NULL | 6620 | Using join buffer |
| 2 | DERIVED | c | eq_ref |
PRIMARY,contactid_idx | PRIMARY | 4
| bm_actual.contract_id | 1 | Using where |
| 2 | DERIVED | cfp | eq_ref |
contract_id | contract_id | 4
| bm_actual.contract_id | 1 | |
| 2 | DERIVED | bm | eq_ref |
PRIMARY,profileid_idx | PRIMARY | 4
| bm_actual.id | 1 | |
| 2 | DERIVED | bp | eq_ref |
PRIMARY | PRIMARY | 4
| billing.bm.billing_profile_id | 1 | |
| 4 | DERIVED | <derived5> | ALL |
NULL | NULL | NULL
| NULL | 6620 | Using temporary; Using filesort |
| 4 | DERIVED | bm1 | ref |
contractid_idx,billing_mappings_start_date | contractid_idx | 4
| max.contract_id | 1 | Using where |
| 5 | DERIVED | bm2 | index |
billing_mappings_start_date | contractid_idx | 4
| NULL | 7277 | Using where |
| 3 | DERIVED | NULL | NULL |
NULL | NULL | NULL
| NULL | NULL | No tables used |
+----+-------------+------------+--------+--------------------------------------------+-------------------+---------+-------------------------------+------+---------------------------------+
14 rows in set (0.13 sec)
I leave a copy here if you can't see it right:
https://nopaste.linux-dev.org/?1173341
Thanks in advance
El 25/01/2018 a las 15:07, Alex Lutay escribió:
> Hi,
>
> Before studying it further, can you please share EXPLAIN for the query
> you have shared (just re-execute the SELECT query adding EXPLAIN in
> front of the query).
>
> Tnx!
>
> On 01/25/2018 03:04 PM, Alejandro Grijalba wrote:
> ...
>> I have read that fraud calculation queries were already optimized in
>> previous release, so what is wrong?
> ...
>
More information about the Spce-user
mailing list