What indexes will I need to make on this SQL Server table?

Question

Hi folks,

I have the following sql query for a SQL Server 2008 db.

SELECT TOP(@NumberOfStreetResults) LocationType, LocationId, Name
FROM [dbo].[LocationNames] 
WHERE CONTAINS(Name, @SearchQuery)
    AND LocationType = 7

Notice how I'm using the CONTAINS keyword? I have an FTS on the Name field.

I'm not sure what index(s) I need to manually add to the table because this query is very common in our system.

Do I need to just add an index against LocationType?

Update

Here's the query graphs...

Answer 1

Simply an index is a temporary table in SQL which keeps a copy of indexed column sorted. When a new row is inserted SQL puts a new record in temp table at the correct place so an index has the following results:

A) Advantages:

1) Increase in search speed because the table is sorted in a temp table based on indexed field(s)

B) Disadvantages:

1) Slow down CRUD (Create, Read, Update, Delete) because same actions should be done on temp tables if needed.

2) DataBase size increases because of using temp tables.

Conclusion:

Use indexing on fields which you frequently refer to them as the search criteria (WHERE)

Answer 2

IF your LocationType is highly selective then my suggestion is to create a covered index on that table with key as LocationType and add LocationId in your include column list