0

I've made an aura component that people can search for records. I'm running into speed issues with the query i'm using. Searchkey is the input the user is typing in.

select Fields  from Order where Project_ID__c LIKE: searchKey or Account.ShippingStreet LIKE: searchkey or Account.Name LIKE: searchkey or Account.AccountNumber LIKE: searchkey order by createdDate DESC limit 200

I've looked into sosl but from what i understand you can't specify specific fields and related object fields. Any tips for making this go a little faster or am i doomed to slowness. I suspect it has to do with having 4 likes in the Where but i can't think of a better way to go about this.

Thanks!

1 Answer 1

2

Full table scans are a bad idea, which is what you get with a LIKE. Even if you had only 1, you'd be dealing with relative slowness. Use two or three queries instead. First, SOSL for all Orders where Project_Id__c matches the search term. Next, SOSL for all Accounts that match any of the three fields. Third, SOQL to get any orders that match those results.

Account[] accounts = [FIND :term IN ALL FIELDS RETURNING
Account(Id WHERE Name LIKE :term OR ShippingStreet LIKE :term OR AccountNumber LIKE :term)][0];

Order[] orders = [FIND :term IN ALL FIELDS RETURNING
Order(Id WHERE Project_Id__c LIKE :term)][0];

Order[] results = [SELECT fields FROM Order WHERE Id IN :orders OR AccountId IN :accounts;

Keep in mind that wildcards at the beginning of a term don't work the way you expect, because search indexes start from the first letter of each word. You need to restrict those types of searches, or at least warn users that the results may not be what they expect.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .