MariaDB uses wrong index when searching for phone_number

All installation and configuration problems and questions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

MariaDB uses wrong index when searching for phone_number

Postby ccabrera » Wed Oct 25, 2017 7:47 pm

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,
Christian Cabrera
Enlaza Comunicaciones - Vicidial Partner
Mexico City
ccabrera
 
Posts: 153
Joined: Fri Jan 14, 2011 7:53 pm
Location: Mexico City

Re: MariaDB uses wrong index when searching for phone_number

Postby mflorell » Thu Oct 26, 2017 12:23 am

How many records are in vicidial_list?

What hardware are you using for your database server?
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: MariaDB uses wrong index when searching for phone_number

Postby ccabrera » Thu Oct 26, 2017 12:34 am

Hello Matt,

Vicidial list is 3'398,241.

Server is Intel(R) Xeon(R) CPU E5-2630 v2 @ 2.60GHz with 12 cores, 32 GB RAM and 128 GB SAS 15K RPM HD.

Maximum system stats tells me there are 20 agents at maximum.

Load is, at most, 1.5.


Even though there are no agents at the server right now, I can run the queries again and get the same results as before.
Christian Cabrera
Enlaza Comunicaciones - Vicidial Partner
Mexico City
ccabrera
 
Posts: 153
Joined: Fri Jan 14, 2011 7:53 pm
Location: Mexico City

Re: MariaDB uses wrong index when searching for phone_number

Postby mflorell » Thu Oct 26, 2017 6:13 am

With over 3 million leads on a database server with only a single spindle hard drive I'm not surprised at all by the issues you're having.

I would recommend either removing leads or getting better database hardware. For larger systems we recommend LSI Logic MegaRAID cards with SSD drives.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida


Return to Support

Who is online

Users browsing this forum: No registered users and 76 guests