Searching for a lead by name crashes Database

[Iz3k34l]

Ok so i have been having a problem where (at first seemed for no apparent reason) the dialer would lock up and calls would stop and agents would not be able to do anything in Vicidial. Turns out that a MySQL query would hang or at least take a really long time to finish which would cause other queries to be on hold etc... causing the problems as described above. So after going through the slow query logs and find some offending slow queries and tracking them down to where they were called from, it turns our that it is from the admin_search_lead.php page when using the option to search for a lead by name (first or last or both), but does not happen when a lead is searched by phone number here is an example query;

Code: Select all

# Time: 140801 13:13:36
# User@Host: cron[cron] @ localhost []
# Thread_id: 711268  Schema: asterisk  QC_hit: No
# Query_time: 35.627407  Lock_time: 0.000116  Rows_sent: 544  Rows_examined: 7314300
SET timestamp=1406916816;
SELECT lead_id,entry_date,modify_date,status,user,vendor_lead_code,source_id,list_id,gmt_offset_now,called_since_last_reset,phone_code,phone_number,title,first_name,middle_initial,last_name,address1,address2,address3,city,state,province,postal_code,country_code,gender,date_of_birth,alt_phone,email,security_phrase,comments,called_count,last_local_call_time,rank,owner from vicidial_list where   last_name='STINSON'  order by modify_date desc limit 1000;


The vicidial_list table is about 9 million records, i copied and pasted the query in phpmyadmin to confirm the query locks up the database and it in fact does. So what i also found is that it seems to lock up when its "State" ( as observed in mtop) is "sending".

Lastly, purging or pruning the list table at this point is not an option. Any thoughts are greatly appreciated

[Iz3k34l]

UPDATE:

I changed the query string to 'limit 5' and it doesn't kill everything, however the query still takes a about 5-10 seconds to return results

[williamconley]

UPDATE:

I changed the query string to 'limit 5' and it doesn't kill everything, however the query still takes a about 5-10 seconds to return results

index your last name field. (when the dialer is not in use, it'll probably take a while to generate the index).

Code: Select all

ALTER TABLE `asterisk`.`vicidial_list` ADD INDEX `last_name` ( `last_name` )

Yes, this will add load any time a new record is created ... but that will be negligible (less than a nanosecond).

Yes, this will take some extra disk space, but if you check the existing index files, that, too is negligible.

But this should make looking up by this field happen MUCH freakin' faster.

[geoff3dmg]

ugh, why isn't that indexed as default..

[mflorell]

There are already 10 defaulted indexes in vicidial_list. Every index you add adds to the time it takes to write/modify to a table, not to mention the time to rebuild/optimize the table. One more index wouldn't affect things that much, but we saw usage of that specific searching feature as very low, since that is the only thing that would use that index, and we decided not to affect all systems just for the few that use it.

[Iz3k34l]

Thanks for the suggestion William, i will run it this evening when everyone is gone

[williamconley]

Considering that I've only had two clients (in several years) need to index that field, I can see why it's not already indexed. Can't index everything for everyone, just the fields needed for those that need them. Otherwise the entire system would bog down.

It would be interesting to put in a response time check for searches and offer the suggestion to index a field for slow-responding systems. Or link to a topic discussing the situation.

[Iz3k34l]

William,

that worked beautifully, the query return lightning fast.

thank you

[Iz3k34l]

Upadate: I added an index for the 'user' field to the same table. The supervisors were using the search to list the agents sales, the status field was already indexed the but the user field was not, which caused the same (not as bad) problems as before. After the change the results returned very fast with no hit to the DB.