0

I have this following code in my repository layer, This code basically gets the string and returns the list of objects containing the string in an email field.

  public async Task<IEnumerable<UserTable>> GetUsers(string userSearch)
        {
            var item = await riskDBContext.UserTables.Where(e=>e.Email.Contains(userSearch)).ToListAsync(); ;
            return item;
        }

The User table contains like 50000 records. If the column email is indexed will this search translate faster in the SQL query?

Please note email is not the primary key. Basically I am implementing the auto-search functionality and when the user types in 3 characters or more it starts calling this function.

Since I am using Linq, I assume the following function will translate into the equivalent query.

select * from usertable where email like '%userSearch%'

Apart from indexing the email column should I be doing anything else? is this the right way to do for querying?

Venkat
  • 1,702
  • 2
  • 27
  • 47

1 Answers1

0

If the column email is indexed will this search translate faster in the SQL query?

No, index will be used only if you use StartsWith

e.Email.StartsWith(userSearch)

For better performance you can use FreeText Table and how to use it with EF Core

Or install somewhere Elasticsearch server, synchronise with SQL Server data and use it for fast and rich search capabilities.

Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32