<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class=""><br class=""><div class="">
<div style="text-align: start; text-indent: 0px; word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class=""><div style="text-align: start; text-indent: 0px; word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class=""><div style="color: rgb(0, 0, 0); letter-spacing: normal; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; font-family: Helvetica; font-style: normal; font-variant-caps: normal; font-weight: normal; text-align: start; text-indent: 0px;" class=""><font size="3" class="">Hi,</font></div><div style="color: rgb(0, 0, 0); letter-spacing: normal; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; font-family: Helvetica; font-style: normal; font-variant-caps: normal; font-weight: normal; text-align: start; text-indent: 0px;" class=""><font size="3" class=""><br class=""></font></div><div style="text-align: start; text-indent: 0px;" class=""><font size="3" class="">I actually moved my database to external server and optimised some indexes to make this faster (but SSD and more memory definitely helps)</font></div><div style="text-align: start; text-indent: 0px;" class=""><font size="3" class=""><br class=""></font></div><div style="text-align: start; text-indent: 0px;" class=""><font size="3" class="">While you (Sipwise) are at it to optimise this query, you may also want to consider to calculate this not from the 1st of the month, but from a month before, until now…</font></div><div style="text-align: start; text-indent: 0px;" class=""><font size="3" class=""><br class=""></font></div><div style="text-align: start; text-indent: 0px;" class=""><font size="3" class="">so if you do the calculation today it should run from 27 Dec to 26 Jan (this time) - so that it’s a more accurate period, otherwise in the beginning of the month somebody can do a huge amount of fraud, and at the end of the month they run out immediately. Same for the daily limits, they should be from yesterday, current time, to now.</font></div><div style="text-align: start; text-indent: 0px;" class=""><font size="3" class=""><br class=""></font></div><div style="text-align: start; text-indent: 0px;" class=""><font size="3" class="">as in this example.</font></div><div style="text-align: start; text-indent: 0px;" class=""><font size="3" class=""><br class=""></font></div><table class="x_MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="400" style="color: rgb(0, 0, 0); letter-spacing: normal; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; font-family: Helvetica; text-indent: 0px; width: 259.8pt;"><tbody class=""><tr class=""><td valign="top" class="" style="padding: 0in;"><blockquote type="cite" style="font-size: 14px;" class=""><blockquote type="cite" cite="mid:020e01d395f3$852ce5a0$8f86b0e0$@sipwise.com" class="" style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; background-color: rgb(255, 255, 255);"><pre wrap="" class="">WHERE CASE WHEN bpinfo.interval = 'month'
THEN cdr.start_time
BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-%d %H:%i:%s'))
AND UNIX_TIMESTAMP(DATE_FORMAT(NOW(),
'%Y-%m-%d %H:%i:%s'))-1
ELSE cdr.start_time
BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 DAY, '%Y-%m-%d %H:%i:%S'))
AND UNIX_TIMESTAMP(DATE_FORMAT(NOW(),
'%Y-%m-%d %H:%i:%S'))
END
</pre></blockquote></blockquote><div class=""><font color="#5856d6" face="monospace" class="">Does that make sense ?</font></div><div class=""><font color="#5856d6" face="monospace" class=""><br class=""></font></div><div class=""><font color="#5856d6" face="monospace" class="">Thanks</font></div><div class=""><font color="#5856d6" face="monospace" class="">Walter</font></div><div class=""><font color="#5856d6" face="monospace" class=""><br class=""><br class=""><br class=""><span style="white-space: pre-wrap;" class=""><br class=""></span></font><blockquote type="cite" cite="mid:020e01d395f3$852ce5a0$8f86b0e0$@sipwise.com" class="" style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; background-color: rgb(255, 255, 255);"><pre wrap="" class=""><br class=""></pre></blockquote></div></td></tr></tbody></table></div></div>
</div>
<div><br class=""><blockquote type="cite" class=""><div class="">On 26 Jan 2018, at 12:08 AM, Alejandro Grijalba <<a href="mailto:agrijalba@innovasur.es" class="">agrijalba@innovasur.es</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="moz-cite-prefix" style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);">10 minutes and counting so far :(<br class=""><br class=""><br class="">El 25/01/2018 a las 16:45, Rene Krenn escribió:<br class=""></div><blockquote type="cite" cite="mid:020e01d395f3$852ce5a0$8f86b0e0$@sipwise.com" style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);" class=""><pre wrap="" class="">Hi,
thanks for pointing.
Can you try running this modified query below on your test database a few times,
and report back how long it took (longer or less 2 minutes)?
SELECT <a href="http://bpinfo.id/" style="color: rgb(255, 38, 0);" class="">bpinfo.id</a>, 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,
bpinfo.interval_notify,
bpinfo.fraud_use_reseller_rates as use_reseller_rates
FROM (
SELECT <a href="http://c.id/" style="color: rgb(255, 38, 0);" class="">c.id</a>, 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,
'account_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_interval_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 <a href="http://ca.id/" style="color: rgb(255, 38, 0);" class="">ca.id</a> as contract_id,
(select <a href="http://bma.id/" style="color: rgb(255, 38, 0);" class="">bma.id</a> from billing.billing_mappings bma where
bma.contract_id=<a href="http://ca.id/" style="color: rgb(255, 38, 0);" class="">ca.id</a> and
(bma.end_date >= NOW() OR bma.end_date IS NULL)
AND (bma.start_date <= NOW() OR bma.start_date IS NULL) order
by bma.start_date desc, <a href="http://bma.id/" style="color: rgb(255, 38, 0);" class="">bma.id</a> desc limit 1) as id from
billing.contracts ca
) AS bm_actual ON bm_actual.contract_id = <a href="http://c.id/" style="color: rgb(255, 38, 0);" class="">c.id</a>
JOIN billing.billing_mappings bm ON <a href="http://bm.id/" style="color: rgb(255, 38, 0);" class="">bm.id</a> = <a href="http://bm_actual.id/" style="color: rgb(255, 38, 0);" class="">bm_actual.id</a>
JOIN billing.billing_profiles bp ON <a href="http://bp.id/" style="color: rgb(255, 38, 0);" class="">bp.id</a> = bm.billing_profile_id
JOIN billing.contacts n ON <a href="http://n.id/" style="color: rgb(255, 38, 0);" class="">n.id</a> = c.contact_id
JOIN billing.resellers r ON <a href="http://r.id/" style="color: rgb(255, 38, 0);" class="">r.id</a> = n.reseller_id
LEFT JOIN billing.contract_fraud_preferences cfp
ON cfp.contract_id = <a href="http://c.id/" style="color: rgb(255, 38, 0);" class="">c.id</a>
WHERE c.status = 'active'
HAVING interval_limit > 0
) AS bpinfo
JOIN accounting.cdr ON cdr.source_account_id = <a href="http://bpinfo.id/" style="color: rgb(255, 38, 0);" class="">bpinfo.id</a>
WHERE CASE WHEN bpinfo.interval = '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 <a href="http://bpinfo.id/" style="color: rgb(255, 38, 0);" class="">bpinfo.id</a>
HAVING interval_cost >= interval_limit;
thanks®ards,
rene
</pre></blockquote><br style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);" class=""><br style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);" class=""><div class="moz-signature" style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);">--<span class="Apple-converted-space"> </span><br class=""><p class="firma" style="color: rgb(97, 128, 148);"><span class="nombre" style="font-size: 18px; font-weight: normal;">Alejandro Grijalba Martínez</span><br class=""><span style="color: rgb(102, 102, 102);" class="">Departamento de Sistemas</span><br class=""></p><p class=""><span id="cid:part1.B663A4A5.3A1E4332@innovasur.es"><firma_correo.png></span></p><span class="letra" style="color: rgb(255, 38, 0);">E</span><span class="Apple-converted-space"> </span><span class=""><a class="moz-txt-link-abbreviated" href="mailto:agrijalba@innovasur.com" style="color: rgb(255, 38, 0);">agrijalba@innovasur.com</a></span><br class=""><span class="letra" style="color: rgb(255, 38, 0);">D</span><span class="Apple-converted-space"> </span><span class="">Calle Sierra Morena 12 A Edif.CTSAII. Pta. 2º, Oficina 9. 23620 Mengíbar (Jaén) / España.<span class="Apple-converted-space"> </span><br class=""><span class="letra" style="color: rgb(255, 38, 0);">T</span><span class="Apple-converted-space"> </span><a href="tel:+34953267919" style="color: rgb(255, 38, 0);" class="">+34 953 267 919</a><br class=""><br class=""><span class="letra" style="color: rgb(255, 38, 0);"><a href="http://www.innovasur.com/" style="color: rgb(255, 38, 0);" class="">www.innovasur.com</a></span></span></div><span style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); float: none; display: inline !important;" class="">_______________________________________________</span><br style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);" class=""><span style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); float: none; display: inline !important;" class="">Spce-user mailing list</span><br style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);" class=""><a href="mailto:Spce-user@lists.sipwise.com" style="color: rgb(255, 38, 0); font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);" class="">Spce-user@lists.sipwise.com</a><br style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);" class=""><a href="https://lists.sipwise.com/listinfo/spce-user" style="color: rgb(255, 38, 0); font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);" class="">https://lists.sipwise.com/listinfo/spce-user</a><br style="font-family: Helvetica, "Trebuchet MS", Arial, sans-serif; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255);" class=""></div></blockquote></div><br class=""></body></html>