Monster Hopper Query locking database table

[EXviciman]

YO YO YO EX Vici - MAN is in the house ,

My Machine:

Asterisk 11.25.3-vici built by abuild @ lamb68 on a x86_64 running Linux on
ViciBox v.8.0.1
VERSION: 2.14-675a
BUILD: 180520-1749

The table vicidial_list contains around ~500k records

My Issue:

I found this HUGE QUERY:

Code: Select all

SELECT lead_id,list_id,gmt_offset_now,phone_number,state,status,modify_date,user,vendor_lead_code FROM vicidial_list where ( ( (called_since_last_reset IN('Y','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9')) and (status='N') and (( (gmt_offset_now='13.00') and (last_local_call_time < "2019-09-09 01:24:02") ) or ( (gmt_offset_now='12.75') and (last_local_call_time < "2019-09-09 01:09:02") ) or ( (gmt_offset_now='12.50') and (last_local_call_time < "2019-09-09 00:54:02") ) or ( (gmt_offset_now='12.25') and (last_local_call_time < "2019-09-09 00:39:02") ) or ( (gmt_offset_now='12.00') and (last_local_call_time < "2019-09-09 00:24:02") ) or ( (gmt_offset_now='11.75') and (last_local_call_time < "2019-09-09 00:09:02") ) or ( (gmt_offset_now='11.50') and (last_local_call_time < "2019-09-08 23:54:02") ) or ( (gmt_offset_now='11.25') and (last_local_call_time < "2019-09-08 23:39:02") ) or ( (gmt_offset_now='11.00') and (last_local_call_time < "2019-09-08 23:24:02") ) or ( (gmt_offset_now='10.75') and (last_local_call_time < "2019-09-08 23:09:02") ) or ( (gmt_offset_now='10.50') and (last_local_call_time < "2019-09-08 22:54:02") ) or ( (gmt_offset_now='10.25') and (last_local_call_time < "2019-09-08 22:39:02") ) or ( (gmt_offset_now='10.00') and (last_local_call_time < "2019-09-08 22:24:02") ) or ( (gmt_offset_now='9.75') and (last_local_call_time < "2019-09-08 22:09:02") ) or ( (gmt_offset_now='9.50') and (last_local_call_time < "2019-09-08 21:54:02") ) or ( (gmt_offset_now='9.25') and (last_local_call_time < "2019-09-08 21:39:02") ) or ( (gmt_offset_now='9.00') and (last_local_call_time < "2019-09-08 21:24:02") ) or ( (gmt_offset_now='8.75') and (last_local_call_time < "2019-09-08 21:09:02") ) or ( (gmt_offset_now='8.50') and (last_local_call_time < "2019-09-08 20:54:02") ) or ( (gmt_offset_now='8.25') and (last_local_call_time < "2019-09-08 20:39:02") ) or ( (gmt_offset_now='8.00') and (last_local_call_time < "2019-09-08 20:24:02") ) or ( (gmt_offset_now='7.75') and (last_local_call_time < "2019-09-08 20:09:02") ) or ( (gmt_offset_now='7.50') and (last_local_call_time < "2019-09-08 19:54:02") ) or ( (gmt_offset_now='7.25') and (last_local_call_time < "2019-09-08 19:39:02") ) or ( (gmt_offset_now='7.00') and (last_local_call_time < "2019-09-08 19:24:02") ) or ( (gmt_offset_now='6.75') and (last_local_call_time < "2019-09-08 19:09:02") ) or ( (gmt_offset_now='6.50') and (last_local_call_time < "2019-09-08 18:54:02") ) or ( (gmt_offset_now='6.25') and (last_local_call_time < "2019-09-08 18:39:02") ) or ( (gmt_offset_now='6.00') and (last_local_call_time < "2019-09-08 18:24:02") ) or ( (gmt_offset_now='5.75') and (last_local_call_time < "2019-09-08 18:09:02") ) or ( (gmt_offset_now='5.50') and (last_local_call_time < "2019-09-08 17:54:02") ) or ( (gmt_offset_now='5.25') and (last_local_call_time < "2019-09-08 17:39:02") ) or ( (gmt_offset_now='5.00') and (last_local_call_time < "2019-09-08 17:24:02") ) or ( (gmt_offset_now='4.75') and (last_local_call_time < "2019-09-08 17:09:02") ) or ( (gmt_offset_now='4.50') and (last_local_call_time < "2019-09-08 16:54:02") ) or ( (gmt_offset_now='4.25') and (last_local_call_time < "2019-09-08 16:39:02") ) or ( (gmt_offset_now='4.00') and (last_local_call_time < "2019-09-08 16:24:02") ) or ( (gmt_offset_now='3.75') and (last_local_call_time < "2019-09-08 16:09:02") ) or ( (gmt_offset_now='3.50') and (last_local_call_time < "2019-09-08 15:54:02") ) or ( (gmt_offset_now='3.25') and (last_local_call_time < "2019-09-08 15:39:02") ) or ( (gmt_offset_now='3.00') and (last_local_call_time < "2019-09-08 15:24:02") ) or ( (gmt_offset_now='2.75') and (last_local_call_time < "2019-09-08 15:09:02") ) or ( (gmt_offset_now='2.50') and (last_local_call_time < "2019-09-08 14:54:02") ) or ( (gmt_offset_now='2.25') and (last_local_call_time < "2019-09-08 14:39:02") ) or ( (gmt_offset_now='2.00') and (last_local_call_time < "2019-09-08 14:24:02") ) or ( (gmt_offset_now='1.75') and (last_local_call_time < "2019-09-08 14:09:02") ) or ( (gmt_offset_now='1.50') and (last_local_call_time < "2019-09-08 13:54:02") ) or ( (gmt_offset_now='1.25') and (last_local_call_time < "2019-09-08 13:39:02") ) or ( (gmt_offset_now='1.00') and (last_local_call_time < "2019-09-08 13:24:02") ) or ( (gmt_offset_now='0.75') and (last_local_call_time < "2019-09-08 13:09:02") ) or ( (gmt_offset_now='0.50') and (last_local_call_time < "2019-09-08 12:54:02") ) or ( (gmt_offset_now='0.25') and (last_local_call_time < "2019-09-08 12:39:02") ) or ( (gmt_offset_now='0.00') and (last_local_call_time < "2019-09-08 12:24:02") ) or ( (gmt_offset_now='-0.25') and (last_local_call_time < "2019-09-08 12:09:02") ) or ( (gmt_offset_now='-0.50') and (last_local_call_time < "2019-09-08 11:54:02") ) or ( (gmt_offset_now='-0.75') and (last_local_call_time < "2019-09-08 11:39:02") ) or ( (gmt_offset_now='-1.00') and (last_local_call_time < "2019-09-08 11:24:02") ) or ( (gmt_offset_now='-1.25') and (last_local_call_time < "2019-09-08 11:09:02") ) or ( (gmt_offset_now='-1.50') and (last_local_call_time < "2019-09-08 10:54:02") ) or ( (gmt_offset_now='-1.75') and (last_local_call_time < "2019-09-08 10:39:02") ) or ( (gmt_offset_now='-2.00') and (last_local_call_time < "2019-09-08 10:24:02") ) or ( (gmt_offset_now='-2.25') and (last_local_call_time < "2019-09-08 10:09:02") ) or ( (gmt_offset_now='-2.50') and (last_local_call_time < "2019-09-08 09:54:02") ) or ( (gmt_offset_now='-2.75') and (last_local_call_time < "2019-09-08 09:39:02") ) or ( (gmt_offset_now='-3.00') and (last_local_call_time < "2019-09-08 09:24:02") ) or ( (gmt_offset_now='-3.25') and (last_local_call_time < "2019-09-08 09:09:02") ) or ( (gmt_offset_now='-3.50') and (last_local_call_time < "2019-09-08 08:54:02") ) or ( (gmt_offset_now='-3.75') and (last_local_call_time < "2019-09-08 08:39:02") ) or ( (gmt_offset_now='-4.00') and (last_local_call_time < "2019-09-08 08:24:02") ) or ( (gmt_offset_now='-4.25') and (last_local_call_time < "2019-09-08 08:09:02") ) or ( (gmt_offset_now='-4.50') and (last_local_call_time < "2019-09-08 07:54:02") ) or ( (gmt_offset_now='-4.75') and (last_local_call_time < "2019-09-08 07:39:02") ) or ( (gmt_offset_now='-5.00') and (last_local_call_time < "2019-09-08 07:24:02") ) or ( (gmt_offset_now='-5.25') and (last_local_call_time < "2019-09-08 07:09:02") ) or ( (gmt_offset_now='-5.50') and (last_local_call_time < "2019-09-08 06:54:02") ) or ( (gmt_offset_now='-5.75') and (last_local_call_time < "2019-09-08 06:39:02") ) or ( (gmt_offset_now='-6.00') and (last_local_call_time < "2019-09-08 06:24:02") ) or ( (gmt_offset_now='-6.25') and (last_local_call_time < "2019-09-08 06:09:02") ) or ( (gmt_offset_now='-6.50') and (last_local_call_time < "2019-09-08 05:54:02") ) or ( (gmt_offset_now='-6.75') and (last_local_call_time < "2019-09-08 05:39:02") ) or ( (gmt_offset_now='-7.00') and (last_local_call_time < "2019-09-08 05:24:02") ) or ( (gmt_offset_now='-7.25') and (last_local_call_time < "2019-09-08 05:09:02") ) or ( (gmt_offset_now='-7.50') and (last_local_call_time < "2019-09-08 04:54:02") ) or ( (gmt_offset_now='-7.75') and (last_local_call_time < "2019-09-08 04:39:02") ) or ( (gmt_offset_now='-8.00') and (last_local_call_time < "2019-09-08 04:24:02") ) or ( (gmt_offset_now='-8.25') and (last_local_call_time < "2019-09-08 04:09:02") ) or ( (gmt_offset_now='-8.50') and (last_local_call_time < "2019-09-08 03:54:02") ) or ( (gmt_offset_now='-8.75') and (last_local_call_time < "2019-09-08 03:39:02") ) or ( (gmt_offset_now='-9.00') and (last_local_call_time < "2019-09-08 03:24:02") ) or ( (gmt_offset_now='-9.25') and (last_local_call_time < "2019-09-08 03:09:02") ) or ( (gmt_offset_now='-9.50') and (last_local_call_time < "2019-09-08 02:54:02") ) or ( (gmt_offset_now='-9.75') and (last_local_call_time < "2019-09-08 02:39:02") ) or ( (gmt_offset_now='-10.00') and (last_local_call_time < "2019-09-08 02:24:02") ) or ( (gmt_offset_now='-10.25') and (last_local_call_time < "2019-09-08 02:09:02") ) or ( (gmt_offset_now='-10.50') and (last_local_call_time < "2019-09-08 01:54:02") ) or ( (gmt_offset_now='-10.75') and (last_local_call_time < "2019-09-08 01:39:02") ) or ( (gmt_offset_now='-11.00') and (last_local_call_time < "2019-09-08 01:24:02") ) or ( (gmt_offset_now='-11.25') and (last_local_call_time < "2019-09-08 01:09:02") ) or ( (gmt_offset_now='-11.50') and (last_local_call_time < "2019-09-08 00:54:02") ) or ( (gmt_offset_now='-11.75') and (last_local_call_time < "2019-09-08 00:39:02") ) or ( (gmt_offset_now='-12.00') and (last_local_call_time < "2019-09-08 00:24:02") ) or ( (gmt_offset_now='-12.25') and (last_local_call_time < "2019-09-08 00:09:02") ) or ( (gmt_offset_now='-12.50') and (last_local_call_time < "2019-09-07 23:54:02") ) or ( (gmt_offset_now='-12.75') and (last_local_call_time < "2019-09-07 23:39:02") ) ) ) or ( (called_since_last_reset IN('Y','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9')) and (status='NA') and (( (gmt_offset_now='13.00') and (last_local_call_time < "2019-09-09 01:24:02") ) or ( (gmt_offset_now='12.75') and (last_local_call_time < "2019-09-09 01:09:02") ) or ( (gmt_offset_now='12.50') and (last_local_call_time < "2019-09-09 00:54:02") ) or ( (gmt_offset_now='12.25') and (last_local_call_time < "2019-09-09 00:39:02") ) or ( (gmt_offset_now='12.00') and (last_local_call_time < "2019-09-09 00:24:02") ) or ( (gmt_offset_now='11.75') and (last_local_call_time < "2019-09-09 00:09:02") ) or ( (gmt_offset_now='11.50') and (last_local_call_time < "2019-09-08 23:54:02") ) or ( (gmt_offset_now='11.25') and (last_local_call_time < "2019-09-08 23:39:02") ) or ( (gmt_offset_now='11.00') and (last_local_call_time < "2019-09-08 23:24:02") ) or ( (gmt_offset_now='10.75') and (last_local_call_time < "2019-09-08 23:09:02") ) or ( (gmt_offset_now='10.50') and (last_local_call_time < "2019-09-08 22:54:02") ) or ( (gmt_offset_now='10.25') and (last_local_call_time < "2019-09-08 22:39:02") ) or ( (gmt_offset_now='10.00') and (last_local_call_time < "2019-09-08 22:24:02") ) or ( (gmt_offset_now='9.75') and (last_local_call_time < "2019-09-08 22:09:02") ) or ( (gmt_offset_now='9.50') and (last_local_call_time < "2019-09-08 21:54:02") ) or ( (gmt_offset_now='9.25') and (last_local_call_time < "2019-09-08 21:39:02") ) or ( (gmt_offset_now='9.00') and (last_local_call_time < "2019-09-08 21:24:02") ) or ( (gmt_offset_now='8.75') and (last_local_call_time < "2019-09-08 21:09:02") ) or ( (gmt_offset_now='8.50') and (last_local_call_time < "2019-09-08 20:54:02") ) or ( (gmt_offset_now='8.25') and (last_local_call_time < "2019-09-08 20:39:02") ) or ( (gmt_offset_now='8.00') and (last_local_call_time < "2019-09-08 20:24:02") ) or ( (gmt_offset_now='7.75') and (last_local_call_time < "2019-09-08 20:09:02") ) or ( (gmt_offset_now='7.50') and (last_local_call_time < "2019-09-08 19:54:02") ) or ( (gmt_offset_now='7.25') and (last_local_call_time < "2019-09-08 19:39:02") ) or ( (gmt_offset_now='7.00') and (last_local_call_time < "2019-09-08 19:24:02") ) or ( (gmt_offset_now='6.75') and (last_local_call_time < "2019-09-08 19:09:02") ) or ( (gmt_offset_now='6.50') and (last_local_call_time < "2019-09-08 18:54:02") ) or ( (gmt_offset_now='6.25') and (last_local_call_time < "2019-09-08 18:39:02") ) or ( (gmt_offset_now='6.00') and (last_local_call_time < "2019-09-08 18:24:02") ) or ( (gmt_offset_now='5.75') and (last_local_call_time < "2019-09-08 18:09:02") ) or ( (gmt_offset_now='5.50') and (last_local_call_time < "2019-09-08 17:54:02") ) or ( (gmt_offset_now='5.25') and (last_local_call_time < "2019-09-08 17:39:02") ) or ( (gmt_offset_now='5.00') and (last_local_call_time < "2019-09-08 17:24:02") ) or ( (gmt_offset_now='4.75') and (last_local_call_time < "2019-09-08 17:09:02") ) or ( (gmt_offset_now='4.50') and (last_local_call_time < "2019-09-08 16:54:02") ) or ( (gmt_offset_now='4.25') and (last_local_call_time < "2019-09-08 16:39:02") ) or ( (gmt_offset_now='4.00') and (last_local_call_time < "2019-09-08 16:24:02") ) or ( (gmt_offset_now='3.75') and (last_local_call_time < "2019-09-08 16:09:02") ) or ( (gmt_offset_now='3.50') and (last_local_call_time < "2019-09-08 15:54:02") ) or ( (gmt_offset_now='3.25') and (last_local_call_time < "2019-09-08 15:39:02") ) or ( (gmt_offset_now='3.00') and (last_local_call_time < "2019-09-08 15:24:02") ) or ( (gmt_offset_now='2.75') and (last_local_call_time < "2019-09-08 15:09:02") ) or ( (gmt_offset_now='2.50') and (last_local_call_time < "2019-09-08 14:54:02") ) or ( (gmt_offset_now='2.25') and (last_local_call_time < "2019-09-08 14:39:02") ) or ( (gmt_offset_now='2.00') and (last_local_call_time < "2019-09-08 14:24:02") ) or ( (gmt_offset_now='1.75') and (last_local_call_time < "2019-09-08 14:09:02") ) or ( (gmt_offset_now='1.50') and (last_local_call_time < "2019-09-08 13:54:02") ) or ( (gmt_offset_now='1.25') and (last_local_call_time < "2019-09-08 13:39:02") ) or ( (gmt_offset_now='1.00') and (last_local_call_time < "2019-09-08 13:24:02") ) or ( (gmt_offset_now='0.75') and (last_local_call_time < "2019-09-08 13:09:02") ) or ( (gmt_offset_now='0.50') and (last_local_call_time < "2019-09-08 12:54:02") ) or ( (gmt_offset_now='0.25') and (last_local_call_time < "2019-09-08 12:39:02") ) or ( (gmt_offset_now='0.00') and (last_local_call_time < "2019-09-08 12:24:02") ) or ( (gmt_offset_now='-0.25') and (last_local_call_time < "2019-09-08 12:09:02") ) or ( (gmt_offset_now='-0.50') and (last_local_call_time < "2019-09-08 11:54:02") ) or ( (gmt_offset_now='-0.75') and (last_local_call_time < "2019-09-08 11:39:02") ) or ( (gmt_offset_now='-1.00') and (last_local_call_time < "2019-09-08 11:24:02") ) or ( (gmt_offset_now='-1.25') and (last_local_call_time < "2019-09-08 11:09:02") ) or ( (gmt_offset_now='-1.50') and (last_local_call_time < "2019-09-08 10:54:02") ) or ( (gmt_offset_now='-1.75') and (last_local_call_time < "2019-09-08 10:39:02") ) or ( (gmt_offset_now='-2.00') and (last_local_call_time < "2019-09-08 10:24:02") ) or ( (gmt_offset_now='-2.25') and (last_local_call_time < "2019-09-08 10:09:02") ) or ( (gmt_offset_now='-2.50') and (last_local_call_time < "2019-09-08 09:54:02") ) or ( (gmt_offset_now='-2.75') and (last_local_call_time < "2019-09-08 09:39:02") ) or ( (gmt_offset_now='-3.00') and (last_local_call_time < "2019-09-08 09:24:02") ) or ( (gmt_offset_now='-3.25') and (last_local_call_time < "2019-09-08 09:09:02") ) or ( (gmt_offset_now='-3.50') and (last_local_call_time < "2019-09-08 08:54:02") ) or ( (gmt_offset_now='-3.75') and (last_local_call_time < "2019-09-08 08:39:02") ) or ( (gmt_offset_now='-4.00') and (last_local_call_time < "2019-09-08 08:24:02") ) or ( (gmt_offset_now='-4.25') and (last_local_call_time < "2019-09-08 08:09:02") ) or ( (gmt_offset_now='-4.50') and (last_local_call_time < "2019-09-08 07:54:02") ) or ( (gmt_offset_now='-4.75') and (last_local_call_time < "2019-09-08 07:39:02") ) or ( (gmt_offset_now='-5.00') and (last_local_call_time < "2019-09-08 07:24:02") ) or ( (gmt_offset_now='-5.25') and (last_local_call_time < "2019-09-08 07:09:02") ) or ( (gmt_offset_now='-5.50') and (last_local_call_time < "2019-09-08 06:54:02") ) or ( (gmt_offset_now='-5.75') and (last_local_call_time < "2019-09-08 06:39:02") ) or ( (gmt_offset_now='-6.00') and (last_local_call_time < "2019-09-08 06:24:02") ) or ( (gmt_offset_now='-6.25') and (last_local_call_time < "2019-09-08 06:09:02") ) or ( (gmt_offset_now='-6.50') and (last_local_call_time < "2019-09-08 05:54:02") ) or ( (gmt_offset_now='-6.75') and (last_local_call_time < "2019-09-08 05:39:02") ) or ( (gmt_offset_now='-7.00') and (last_local_call_time < "2019-09-08 05:24:02") ) or ( (gmt_offset_now='-7.25') and (last_local_call_time < "2019-09-08 05:09:02") ) or ( (gmt_offset_now='-7.50') and (last_local_call_time < "2019-09-08 04:54:02") ) or ( (gmt_offset_now='-7.75') and (last_local_call_time < "2019-09-08 04:39:02") ) or ( (gmt_offset_now='-8.00') and (last_local_call_time < "2019-09-08 04:24:02") ) or ( (gmt_offset_now='-8.25') and (last_local_call_time < "2019-09-08 04:09:02") ) or ( (gmt_offset_now='-8.50') and (last_local_call_time < "2019-09-08 03:54:02") ) or ( (gmt_offset_now='-8.75') and (last_local_call_time < "2019-09-08 03:39:02") ) or ( (gmt_offset_now='-9.00') and (last_local_call_time < "2019-09-08 03:24:02") ) or ( (gmt_offset_now='-9.25') and (last_local_call_time < "2019-09-08 03:09:02") ) or ( (gmt_offset_now='-9.50') and (last_local_call_time < "2019-09-08 02:54:02") ) or ( (gmt_offset_now='-9.75') and (last_local_call_time < "2019-09-08 02:39:02") ) or ( (gmt_offset_now='-10.00') and (last_local_call_time < "2019-09-08 02:24:02") ) or ( (gmt_offset_now='-10.25') and (last_local_call_time < "2019-09-08 02:09:02") ) or ( (gmt_offset_now='-10.50') and (last_local_call_time < "2019-09-08 01:54:02") ) or ( (gmt_offset_now='-10.75') and (last_local_call_time < "2019-09-08 01:39:02") ) or ( (gmt_offset_now='-11.00') and (last_local_call_time < "2019-09-08 01:24:02") ) or ( (gmt_offset_now='-11.25') and (last_local_call_time < "2019-09-08 01:09:02") ) or ( (gmt_offset_now='-11.50') and (last_local_call_time < "2019-09-08 00:54:02") ) or ( (gmt_offset_now='-11.75') and (last_local_call_time < "2019-09-08 00:39:02") ) or ( (gmt_offset_now='-12.00') and (last_local_call_time < "2019-09-08 00:24:02") ) or ( (gmt_offset_now='-12.25') and (last_local_call_time < "2019-09-08 00:09:02") ) or ( (gmt_offset_now='-12.50') and (last_local_call_time < "2019-09-07 23:54:02") ) or ( (gmt_offset_now='-12.75') and (last_local_call_time < "2019-09-07 23:39:02") ) ) ) or ( (called_since_last_reset IN('Y','Y1','Y2','Y3','Y4','Y5','Y6','Y7','Y8','Y9')) and (status='AA') and (( (gmt_offset_now='13.00') and (last_local_call_time < "2019-09-09 01:24:02") ) or ( (gmt_offset_now='12.75') and (last_local_call_time < "2019-09-09 01:09:02") ) or ( (gmt_offset_now='12.50') and (last_local_call_time < "2019-09-09 00:54:02") ) or ( (gmt_offset_now='12.25') and (last_local_call_time < "2019-09-09 00:39:02") ) or ( (gmt_offset_now='12.00') and (last_local_call_time < "2019-09-09 00:24:02") ) or ( (gmt_offset_now='11.75') and (last_local_call_time < "2019-09-09 00:09:02") ) or ( (gmt_offset_now='11.50') and (last_local_call_time < "2019-09-08 23:54:02") ) or ( (gmt_offset_now='11.25') and (last_local_call_time < "2019-09-08 23:39:02") ) or ( (gmt_offset_now='11.00') and (last_local_call_time < "2019-09-08 23:24:02") ) or ( (gmt_offset_now='10.75') and (last_local_call_time < "2019-09-08 23:09:02") ) or ( (gmt_offset_now='10.50') and (last_local_call_time < "2019-09-08 22:54:02") ) or ( (gmt_offset_now='10.25') and (last_local_call_time < "2019-09-08 22:39:02") ) or ( (gmt_offset_now='10.00') and (last_local_call_time < "2019-09-08 22:24:02") ) or ( (gmt_offset_now='9.75') and (last_local_call_time < "2019-09-08 22:09:02") ) or ( (gmt_offset_now='9.50') and (last_local_call_time < "2019-09-08 21:54:02") ) or ( (gmt_offset_now='9.25') and (last_local_call_time < "2019-09-08 21:39:02") ) or ( (gmt_offset_now='9.00') and (last_local_call_time < "2019-09-08 21:24:02") ) or ( (gmt_offset_now='8.75') and (last_local_call_time < "2019-09-08 21:09:02") ) or ( (gmt_offset_now='8.50') and (last_local_call_time < "2019-09-08 20:54:02") ) or ( (gmt_offset_now='8.25') and (last_local_call_time < "2019-09-08 20:39:02") ) or ( (gmt_offset_now='8.00') and (last_local_call_time < "2019-09-08 20:24:02") ) or ( (gmt_offset_now='7.75') and (last_local_call_time < "2019-09-08 20:09:02") ) or ( (gmt_offset_now='7.50') and (last_local_call_time < "2019-09-08 19:54:02") ) or ( (gmt_offset_now='7.25') and (last_local_call_time < "2019-09-08 19:39:02") ) or ( (gmt_offset_now='7.00') and (last_local_call_time < "2019-09-08 19:24:02") ) or ( (gmt_offset_now='6.75') and (last_local_call_time < "2019-09-08 19:09:02") ) or ( (gmt_offset_now='6.50') and (last_local_call_time < "2019-09-08 18:54:02") ) or ( (gmt_offset_now='6.25') and (last_local_call_time < "2019-09-08 18:39:02") ) or ( (gmt_offset_now='6.00') and (last_local_call_time < "2019-09-08 18:24:02") ) or ( (gmt_offset_now='5.75') and (last_local_call_time < "2019-09-08 18:09:02") ) or ( (gmt_offset_now='5.50') and (last_local_call_time < "2019-09-08 17:54:02") ) or ( (gmt_offset_now='5.25') and (last_local_call_time < "2019-09-08 17:39:02") ) or ( (gmt_offset_now='5.00') and (last_local_call_time < "2019-09-08 17:24:02") ) or ( (gmt_offset_now='4.75') and (last_local_call_time < "2019-09-08 17:09:02") ) or ( (gmt_offset_now='4.50') and (last_local_call_time < "2019-09-08 16:54:02") ) or ( (gmt_offset_now='4.25') and (last_local_call_time < "2019-09-08 16:39:02") ) or ( (gmt_offset_now='4.00') and (last_local_call_time < "2019-09-08 16:24:02") ) or ( (gmt_offset_now='3.75') and (last_local_call_time < "2019-09-08 16:09:02") ) or ( (gmt_offset_now='3.50') and (last_local_call_time < "2019-09-08 15:54:02") ) or ( (gmt_offset_now='3.25') and (last_local_call_time < "2019-09-08 15:39:02") ) or ( (gmt_offset_now='3.00') and (last_local_call_time < "2019-09-08 15:24:02") ) or ( (gmt_offset_now='2.75') and (last_local_call_time < "2019-09-08 15:09:02") ) or ( (gmt_offset_now='2.50') and (last_local_call_time < "2019-09-08 14:54:02") ) or ( (gmt_offset_now='2.25') and (last_local_call_time < "2019-09-08 14:39:02") ) or ( (gmt_offset_now='2.00') and (last_local_call_time < "2019-09-08 14:24:02") ) or ( (gmt_offset_now='1.75') and (last_local_call_time < "2019-09-08 14:09:02") ) or ( (gmt_offset_now='1.50') and (last_local_call_time < "2019-09-08 13:54:02") ) or ( (gmt_offset_now='1.25') and (last_local_call_time < "2019-09-08 13:39:02") ) or ( (gmt_offset_now='1.00') and (last_local_call_time < "2019-09-08 13:24:02") ) or ( (gmt_offset_now='0.75') and (last_local_call_time < "2019-09-08 13:09:02") ) or ( (gmt_offset_now='0.50') and (last_local_call_time < "2019-09-08 12:54:02") ) or ( (gmt_offset_now='0.25') and (last_local_call_time < "2019-09-08 12:39:02") ) or ( (gmt_offset_now='0.00') and (last_local_call_time < "2019-09-08 12:24:02") ) or ( (gmt_offset_now='-0.25') and (last_local_call_time < "2019-09-08 12:09:02") ) or ( (gmt_offset_now='-0.50') and (last_local_call_time < "2019-09-08 11:54:02") ) or ( (gmt_offset_now='-0.75') and (last_local_call_time < "2019-09-08 11:39:02") ) or ( (gmt_offset_now='-1.00') and (last_local_call_time < "2019-09-08 11:24:02") ) or ( (gmt_offset_now='-1.25') and (last_local_call_time < "2019-09-08 11:09:02") ) or ( (gmt_offset_now='-1.50') and (last_local_call_time < "2019-09-08 10:54:02") ) or ( (gmt_offset_now='-1.75') and (last_local_call_time < "2019-09-08 10:39:02") ) or ( (gmt_offset_now='-2.00') and (last_local_call_time < "2019-09-08 10:24:02") ) or ( (gmt_offset_now='-2.25') and (last_local_call_time < "2019-09-08 10:09:02") ) or ( (gmt_offset_now='-2.50') and (last_local_call_time < "2019-09-08 09:54:02") ) or ( (gmt_offset_now='-2.75') and (last_local_call_time < "2019-09-08 09:39:02") ) or ( (gmt_offset_now='-3.00') and (last_local_call_time < "2019-09-08 09:24:02") ) or ( (gmt_offset_now='-3.25') and (last_local_call_time < "2019-09-08 09:09:02") ) or ( (gmt_offset_now='-3.50') and (last_local_call_time < "2019-09-08 08:54:02") ) or ( (gmt_offset_now='-3.75') and (last_local_call_time < "2019-09-08 08:39:02") ) or ( (gmt_offset_now='-4.00') and (last_local_call_time < "2019-09-08 08:24:02") ) or ( (gmt_offset_now='-4.25') and (last_local_call_time < "2019-09-08 08:09:02") ) or ( (gmt_offset_now='-4.50') and (last_local_call_time < "2019-09-08 07:54:02") ) or ( (gmt_offset_now='-4.75') and (last_local_call_time < "2019-09-08 07:39:02") ) or ( (gmt_offset_now='-5.00') and (last_local_call_time < "2019-09-08 07:24:02") ) or ( (gmt_offset_now='-5.25') and (last_local_call_time < "2019-09-08 07:09:02") ) or ( (gmt_offset_now='-5.50') and (last_local_call_time < "2019-09-08 06:54:02") ) or ( (gmt_offset_now='-5.75') and (last_local_call_time < "2019-09-08 06:39:02") ) or ( (gmt_offset_now='-6.00') and (last_local_call_time < "2019-09-08 06:24:02") ) or ( (gmt_offset_now='-6.25') and (last_local_call_time < "2019-09-08 06:09:02") ) or ( (gmt_offset_now='-6.50') and (last_local_call_time < "2019-09-08 05:54:02") ) or ( (gmt_offset_now='-6.75') and (last_local_call_time < "2019-09-08 05:39:02") ) or ( (gmt_offset_now='-7.00') and (last_local_call_time < "2019-09-08 05:24:02") ) or ( (gmt_offset_now='-7.25') and (last_local_call_time < "2019-09-08 05:09:02") ) or ( (gmt_offset_now='-7.50') and (last_local_call_time < "2019-09-08 04:54:02") ) or ( (gmt_offset_now='-7.75') and (last_local_call_time < "2019-09-08 04:39:02") ) or ( (gmt_offset_now='-8.00') and (last_local_call_time < "2019-09-08 04:24:02") ) or ( (gmt_offset_now='-8.25') and (last_local_call_time < "2019-09-08 04:09:02") ) or ( (gmt_offset_now='-8.50') and (last_local_call_time < "2019-09-08 03:54:02") ) or ( (gmt_offset_now='-8.75') and (last_local_call_time < "2019-09-08 03:39:02") ) or ( (gmt_offset_now='-9.00') and (last_local_call_time < "2019-09-08 03:24:02") ) or ( (gmt_offset_now='-9.25') and (last_local_call_time < "2019-09-08 03:09:02") ) or ( (gmt_offset_now='-9.50') and (last_local_call_time < "2019-09-08 02:54:02") ) or ( (gmt_offset_now='-9.75') and (last_local_call_time < "2019-09-08 02:39:02") ) or ( (gmt_offset_now='-10.00') and (last_local_call_time < "2019-09-08 02:24:02") ) or ( (gmt_offset_now='-10.25') and (last_local_call_time < "2019-09-08 02:09:02") ) or ( (gmt_offset_now='-10.50') and (last_local_call_time < "2019-09-08 01:54:02") ) or ( (gmt_offset_now='-10.75') and (last_local_call_time < "2019-09-08 01:39:02") ) or ( (gmt_offset_now='-11.00') and (last_local_call_time < "2019-09-08 01:24:02") ) or ( (gmt_offset_now='-11.25') and (last_local_call_time < "2019-09-08 01:09:02") ) or ( (gmt_offset_now='-11.50') and (last_local_call_time < "2019-09-08 00:54:02") ) or ( (gmt_offset_now='-11.75') and (last_local_call_time < "2019-09-08 00:39:02") ) or ( (gmt_offset_now='-12.00') and (last_local_call_time < "2019-09-08 00:24:02") ) or ( (gmt_offset_now='-12.25') and (last_local_call_time < "2019-09-08 00:09:02") ) or ( (gmt_offset_now='-12.50') and (last_local_call_time < "2019-09-07 23:54:02") ) or ( (gmt_offset_now='-12.75') and (last_local_call_time < "2019-09-07 23:39:02") ) ) ) ) and ((list_id IN('148','105','106','115'))) and lead_id NOT IN('493868','493871','493872','493873','493874','493875','493876','493877','0')  and ((gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.50','8.25','8.00','7.75','7.50','7.25','7.00','6.75','6.50','6.25','6.00','5.75','5.50','5.25','5.00','4.75','4.50','4.25','4.00','3.75','3.50','3.25','3.00','2.75','2.50','2.25','2.00','1.75','1.50','1.25','1.00','0.75','0.50','0.25','0.00','-0.25','-0.50','-0.75','-1.00','-1.25','-1.50','-1.75','-2.00','-2.25','-2.50','-2.75','-3.00','-3.25','-3.50','-3.75','-4.00','-4.25','-4.50','-4.75','-5.00','-5.25','-5.50','-5.75','-6.00','-6.25','-6.50','-6.75','-7.00','-7.25','-7.50','-7.75','-8.00','-8.25','-8.50','-8.75','-9.00','-9.25','-9.50','-9.75','-10.00','-10.25','-10.50','-10.75','-11.00','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99') ) )    order by lead_id asc limit 20

it runs for like 13 seconds (THAT IS TOO MUCH)
and gives very often 0 records.... Which make it seem like a big resource spender and nuisance query

I checked it out in the PROCESSLIST and that query basically locks the table and make other queries wait too often. From my investigation I realized it is associated with AST_VDhopper.pl which is generating this monster query.
As I am calling just one GMT basically I don't need all this fancy query is there a way to slice it down on config level ?

Or if it requires to change that perl script do you have any good place to start so it doesn't become a 'PROJECT'.


What I tried:

I disabled any lead recycling I had on any ACTIVE campaign on the server - it seemed to improve performance a bit seeing this query less often , but it is still there...

Tried also to add indexes to relevant columns - which didn't help out ( this over 160 lines where clause is just too overwhelming I guess.... )

I made a few tests to see how this affects the agents as this seems to be related to the hopper I made sure that I am on a campaign where I do not actually start dialing with the hopper ,but just use manual dial (it seems that this query though is still running in the background though...)
Which support the claims of the agents that also manual dials & callbacks are affected by that.

please advise.

[ambiorixg12]

Index wont help because a lot (and) and (or), it make the query super heavy, would be good to determine what campaign setting are creating this super query

[mflorell]

1. (you've already done it) Don't use Lead Recycling
2. Only set the lists to Active=Y that you want to dial today. The less number of leads in the active lists in your campaign, the faster the hopper queries will run.
3. Get better hardware and use a dedicated DB server

[williamconley]

YO YO YO EX Vici - MAN is in the house ,

My Machine:

Asterisk 11.25.3-vici built by abuild @ lamb68 on a x86_64 running Linux on
ViciBox v.8.0.1
VERSION: 2.14-675a
BUILD: 180520-1749

hardware? (Core count and speed and RAM)
HD space available?

Tried also to add indexes to relevant columns - which didn't help out ( this over 160 lines where clause is just too overwhelming I guess.... )

Ordinarily 500k leads is not even notable. But without knowing your hardware, I can't say it's large or small. If you have at least 4 cores and 2G RAM, you shouldn't be experiencing a slowdown.

To be clear: Almost every server has this same query. Many servers have millions of records. We don't ordinarily see a problem until at least 5M.

testing: change dialing to 24 hrs in the middle of the day (when you can dial all numbers, regardless of time zone) or you can more deeply restrict your timezones, limit it to a smaller number of zones. Both ends of the spectrum.

Are you sure it's "running" for 13 seconds? Perhaps it's "waiting" for 12.5 seconds and then runs for .5 seconds? Is there often another query in the way?

What are your hopper settings? I see "order by lead_id asc limit 20" which seems like a rather small limit number. You are running the hopper script every minute?

I presume you have not accidentally deleted the "lead_id" index or the "gmt_offset_now" or "last_local_call_time" indexes, of course. As any of those being missing would definitely explain your issue.

Do you reboot nightly?

[EXviciman]

1. (you've already done it) Don't use Lead Recycling

Done this yes.

2. Only set the lists to Active=Y that you want to dial today. The less number of leads in the active lists in your campaign, the faster the hopper queries will run.

That part turned out to be most crucial. Deactivated all unused campaigns + most unused lists - now the hopper is not crazy anymore.

[williamconley]

Good postback!

[VFRDavid]

(Still) Running the following configuration:

VERSION: 2.14-670a
BUILD: 180424-1521
SVN 2973
DB Schema 1542
Clustered system (1 Web/DB, 3 Web/Telephony)

I would like to add an index for the STATUS column in both the vicidial_log and vicidial_closer_log tables. I have some IN_GROUPS that produce calls that do not produce a "one call close" - however - I would like to credit the source of the call if, after the initial inbound call, the lead_id is dispositioned as any of our SALE statuses. We are making anywhere from 300,000 - 650,000 outbound calls a day, and receiving less than 10% of that via inbound. The inbound campaigns are typically funded every 2 - 3 weeks, and the pulling every SALE from the vicidial_list table with a last_local_call_time during the currently funded period is easy (since both status and last_local_call_time are indexed)...but, for records that have their status changed away from SALE (for example, a customer calls back in after the SALE for any reason, and an unknowing rep re-dispos it as something else) - so - I want to be able to look at the vicidial_log table for the same time period to see if it EVER was dispositioned as SALE. The problem with that is - there are too many calls in that time period to produce an efficient query that runs in a short enough amount of time and doesn't cause system disruption / lag / every other query stacked up behind mine. So - my questions are...

1) Is it safe for me to add indexes such as these in thevicidial_log and _closer_log (or, in the future, any other asterisk) tables?.
2) If #1 IS safe, I would imagine, that I should do this after hours, after a successful backup...if my after -hours window isn't that large (a few hours at most), is it safe to add an index in MySQL / MariaDB to a live / in use system?
3) Is there any other way around this - a SELECT directive that will NOT lock things up and cause all other queries to stack up behind mine? From what I have read - I think it's the nature of the asterisk databases and tables storage method that there is no way to select without locking at this point, correct?
3A) One thought I had was to loop through the vicidial_log table one day at a time to build this "ever was a sale" temporary_table - but - even each day's query takes some time - once I get the looping down- I guess I could loop by hour, but - figured the index would avoid all that nonsense...
4) Is there another table that would give me this information, so I can avoid adding these indexes? I tried searching the schema for STATUS - but - nothing really caught my eye there...did I miss some obvious JOIN relationship that would work for me?

I apologize if this is something that I should know prior to "attempting" SELECT statements or "messing around" with MySQL queries...I did try to find out why the world stops until my query is complete - but - again - it seems like it cannot be avoided for long queries on MyISAM tables. For queries that modify data (UPDATE / INSERT / DELETE), I totally get the need for record and/or table locks - but the need for locking on SELECTs isn't immediately intuitive to me - so - I can't help but believe that there is just something that I should know about MariaDB, MyISAM and the SELECT syntax / options - sorry I'm being lazy and asking everyone...so - thank you in advance for any assistance / advice you can provide to me here...I really do appreciate it...

PS - I tried looking at some of the VICIdial reports code to see what the "prepared" SELECTs do there - but didn't see anything obvious there either

David