Author Topic: Autocomplete scalability (Read 2449 times)

Chris Burgess · May 10, 2012, 03:12:06 pm

NB: Screen grab here is from a site running 3.4.8 - I haven't had a chance yet to test this out on more recent CiviCRM.

Sometimes CiviCRM autocomplete seems to slow itself down, running many similar queries at once. Here's a screen grab I caught this morning - it was particularly visible because I'd elevated load on the server already.

Previously I'd thought that this was due to slow typers (blame the users!), but when I tested I found that typing reasonably fast would set the issue off even more. That makes me think that autocomplete could fire its requests after a short delay, rather than immediately on the search box changing. (I haven't reviewed the code there yet.)

Also wondered if there's a way for the autocomplete callback to set a (configurable) time limit on the query, so that if it doesn't return a result in 0.2s, it is killed off rather than left running. I don't think MySQL will let us set query timelimit dynamically; possibly we can track the MySQL query ID in the ajax callback and kill it off if it takes too long.

I know the underlying issue here is really that the DB server load is higher than it ought to be - my question here is how we can improve autocomplete so that it doesn't exacerbate load issues when things do get busy (which is what I think I'm seeing).

Similar questions have been touched on a couple of times before (http://issues.civicrm.org/jira/browse/CRM-6825 and http://forum.civicrm.org/index.php?topic=15679 at least).

PS. The removed query is not a CiviCRM query, it just landed in the middle of the Nigel searches.

Eileen · May 21, 2012, 05:09:41 pm

Chris,

My tests showed that a lot of the problem on those queries were the joins on prefix_id, im, suffix_id.

Basically the id field in the option group table is a varchar & in civicrm_contact prefix_id etc are integers = bad join = slow query on sites that use these fields.

The discussion was that the joins should be replaced by constants (via resolving the pseudoconstants before hand). There was also some talk of having 2 possible join fields in option_value - an integer & a varchar

NB - there are a lot of other places affected by this issue - try converting activity_type to a varchar & doing a before & after activity search on a big DB

xavier · May 21, 2012, 11:08:20 pm

Hi,

If you are non us, you had a bigger load/memory on the server. The latest version (4.2) will fix that.

In my tests, the wildchars on the name & email (%query% was expensive, I wrote a different query that put the wildchar at the end only and search on different fields
https://github.com/TechToThePeople/civicrm_qlookup

The "sql only" better solution would be to have a civicrm_autocomplete with a text field that is a copy of display_name & indexed fulltext (you can't do that with civicm_contact, it's innodb).

The best solution is to switch to an external search, solr, sphynx or http://sna-projects.com/cleo/ (that seems to be made for that)

X+

CiviCRM Community Forums (archive)

News:

Author Topic: Autocomplete scalability (Read 2449 times)

Chris Burgess

Autocomplete scalability

Eileen

Re: Autocomplete scalability

xavier

Re: Autocomplete scalability