0

I have a question about using Order By in my SOQL. My query looks like this 

Select Id,FirstName,LastName from Contact Order By FirstName Limit 100.

Here is what I'm trying to achieve I have a search component which on load shows me a drop down with 100 Contacts and when I type ahead it shows me a list of Contacts that matches the criteria i.e Name.This seems to be working fine however when I was checking the Query performance using Query Plan in Developer Console I noticed the Cost is showing a major difference with and without using Order By Clause.For example I noticed the following numbers.

  1. Using Order By : Cost Rate = 0.5567

  2. Without Order by : Cost Rate = 0.18

Any idea on how to overcome this and by the way I applied filters using where clause I still see this considerable difference.

I have close to 3 Million Contacts in my org.

Improve this question

3 Answers 3

Reset to default
3

As long as the cost is below 1, you have little to worry about.

The main way you would increase your selectivity is by adding filters in your WHERE clause. Even if there is still a difference between the two, your query will be comfortably selective.

You need to make sure your filters all use indexes. Specifically based on your comments, that precludes the use of leading wildcards:

Using Leading % Wildcards

A LIKE condition with a leading % wildcard does not use an index.

SELECT Id FROM Account WHERE Name LIKE '%Acme%'
Improve this answer
4
  • Yeah although my Cost Rate is below 1 when I started testing this in a org with 3 Million Contacts,on type ahead it errors out
    – sfdc
    Mar 14, 2018 at 20:27
  • If you have 3M records, you are going to need to filter on indexed fields. Using ORDER BY or not is a very small straw on the camel's back here.
    – Adrian Larson ♦
    Mar 14, 2018 at 20:35
  • Sorry My bad forgot to include the filter in my query above.I'm actually using a filter Select Id,FirstName,LastName from Contact where Name Like '%Test%' Order By Name Limit 100.I still see the issue
    – sfdc
    Mar 14, 2018 at 20:52
  • @saikirankotha You won't be able to use leading wildcards with this data volume. Maybe it would work with SOSL...
    – Adrian Larson ♦
    Mar 14, 2018 at 21:01
2

If you have a millions of data, you need to use either Salesforce provided index fields or otherwise request Salesforce for custom index for filtering and filter condition should be selective.

For more info, refer Improve SOQL Query Performance - Selectivity and Custom Indexes

Improve this answer
0

I think most appropriate way to do search with large data is SOSL

FIND {searchString} IN NAME FIELDS RETURNING CONTACT
Improve this answer

You must log in to answer this question.

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