Contextual Search uses Zing to perform the search function. Zing is based off Apache Lucene. Apache Lucene has a number of wildcard characters e.g. https://lucene.apache.org/core/2_9_4/queryparsersyntax.html#Wildcard Searches
On a customer instance that was experiencing severe performance degradation on the DB tier, we spotted the following queries in the process list: SELECT ts_word0.`number` FROM ts_word ts_word0 WHERE ts_word0.`word` LIKE '%urgent-disable-account-wright' limit 0,501
This is bad as:
- They were already on the "edge" performance-wise - the DB was being hit with all manner of bad queries and this really wasn't helping
- We knew this wasn't the root cause of my customer's issue but it was a near-constant drain on resources
- ts_word in our case was a 17 million row table and we were doing full index scans due to the "LIKE" condition to perform this query
- Long-running AJAX calls resulted in session synch waits for the user
Steps to Reproduce
- Enable SQL Debug
- Enable Contextual Search on the Incident form
- Configure so that Short Description is used to perform a search against KB
- Open an incident containing a short description such as "***URGENT ISSUE***"
- Observe queries like this in the debug log:
For customers on versions that do NOT have the fix:
Make the customer cognizant that the presence of special characters in fields can cause this issue and address the data in the fields. For version that do not yet have the "fix", consider setting up a BR to cleanup fields containing bad characters or implement UI Scripts/data policies to prevent users from including wildcard characters in fields used for Contextual Search e.g. Incident Short Description.
For customers on versions that DO have the fix:
The "fix" for this incident is that new z-booted instances will the default value for the "Enable wildcard searches" field set false for all Contextual Search Config records (cxs_context_config). However, all out-of-box cxs_context_config records have been set to preserve the original behavior; which means that they are still set to "Enable wildcard searches" = "true". This means that this PRB affects both new and old instances in all versions, even those where the "fix" is present. If your instance is running into excessive latency due to slow queries similar to the following form then you should set "Enable wildcard searches" to "false" for the relevant cxs_context_config record:
SELECT ... FROM ts_word ts_word0 WHERE ts_word0.`word` LIKE '%sitescope' limit 0,501
- Open Contextual Search > Search Contexts
- Open one of the records
- If the "Enable wildcard searches" field is "true", set it to "false" and then click "Update"
Related Problem: PRB660840