MariaDB uses wrong index when searching for phone_number

[ccabrera]

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,

[mflorell]

How many records are in vicidial_list?

What hardware are you using for your database server?

[ccabrera]

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.

[mflorell]

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.