Hello,
I have Vicibox 7 version 2.14-638a BUILD: 171018-2234. I remember reading about this on the forums, but couldn't find the thread, so I apologize for duplicating this issue.
I have some cases where clients report that when using the manual dial search filter, queries can take up to 2 minutes. I've checked the MariaDB slow query log and found many queries like this:
SELECT first_name,last_name,phone_code,phone_number,status,last_local_call_time,lead_id,city,state,postal_code,vendor_lead_code from vicidial_list where (phone_number='XXXXXXXXXX' or alt_phone='XXXXXXXXXX' or address3='XXXXXXXXXX') and list_id IN('1234','1235','1236') order by last_local_call_time desc limit 1000;
This takes 10 seconds to return (when running a EXPLAIN SELECT, the used key is last_local_call_time). So far, I've found this can be fixed in 3 ways:
1) Remove the LIMIT:
SELECT first_name,last_name,phone_code,phone_number,status,last_local_call_time,lead_id,city,state,postal_code,vendor_lead_code from vicidial_list where (phone_number='XXXXXXXXXX' or alt_phone='XXXXXXXXXX' or address3='XXXXXXXXXX') and list_id IN('1234','1235','1236') order by last_local_call_time desc;
When running the EXPLAIN SELECT, the used key is list_id.
2) Increase the LIMIT:
SELECT first_name,last_name,phone_code,phone_number,status,last_local_call_time,lead_id,city,state,postal_code,vendor_lead_code from vicidial_list where (phone_number='XXXXXXXXXX' or alt_phone='XXXXXXXXXX' or address3='XXXXXXXXXX') and list_id IN('1234','1235','1236') order by last_local_call_time desc LIMIT 10000;
When running the EXPLAIN SELECT, the used key is list_id.
3) Force the correct index (list_phone):
SELECT first_name,last_name,phone_code,phone_number,status,last_local_call_time,lead_id,city,state,postal_code,vendor_lead_code from vicidial_list force index (list_phone) where (phone_number='XXXXXXXXXX' or alt_phone='XXXXXXXXXX' or address3='XXXXXXXXXX') and list_id IN('1234','1235','1236') order by last_local_call_time desc;
I'm guessing this is a MariaDB bug since they are the ones choosing the wrong index. list_id or list_phone is much more efficient than last_local_call_time.
Is there any current way to fix this in Vicidial?
Regards,