I have list of cities, around 2712406 records. Each has state code and user first selects state code.
Both StateCode and Name are already indexed.
My current query to Database hits in the form of
return db.Cities.Where(x=>x.StateCode == stateCode && x.Name.StartsWith(name))
.OrderBy( x=> x.Name )
It turned out that combined TEXT INDEX worked well. Joins are expensive operation.
Query performs better then AND of two different result sets.
@CityPath = StateCode + '/' + CharactersTyped + '%' SELECT TOP 10 * FROM Cities WHERE CityPath LIKE @CityPath
In the above query B+ index has to search limited sub-tree as StateCode is itself the root. However in the following query, B+ index has to search larger sub-tree and each result has to be compared for StateCode equivalency. The resultset of B+ index scan for CityName search is larger in this second query, which leads to microsecond timeouts.
@StateCode = StateCode @CityName = CharactersTyped SELECT TOP 10 * FROM Cities WHERE CityName LIKE @CityName AND StateCode = @StateCode
Computed Column Indexes can improve searching of data if data exists in hierarchy as shown above.