Creating Indexes

Question

Learning to create an Index and understanding when one should be created. I am also using Entity Framework 6 and mentioning this in case it adds any other tweaks/complexity which i may not be aware of at this stage.

So far i have the understanding but here is a scenario. I have 3 tables Category, Countries and Products.

By default i have a primary key in all tables called Id. This means an automatic index is created on Id for each column.

I may have a need to search for a single Category or list all of the categories. The search would be based on the name of the category which has a column called Name. So in this instance i would create an Index for the Name column?

The countries table is doing nothing other than having a dropdown listed with all the countries. Considering there is no search function, i could leave the Id as the Index and no need for additional indexes?

Majority of Products are related to a CategoryId (maybe some could be null but in future i could have a Child Category that would be referenced the same way a CategoryID is at present). I have a relationship created for between the CatId in the Products table to the Id column in the Category table. I need to search products by Name, date and description. In this instance i would create an Index for Name, date and description in this order so it first sorts by the name, then date and finally it would search the description.

Finally i want to be able to search a user by a range of parameters which are name, surname, email and zip code. In this instance since the order is important i could create a separate index for each column rather one index and including the 4 columns i listed?

Where i have suggested creating an Index would be a non clustered index.

Would this approach be correct as per my understanding?

Thanks in advance

Answer 1

I may have a need to search for a single Category or list all of the categories. The search would be based on the name of the category which has a column called Name. So in this instance i would create an Index for the Name column?

Yes, but two qualifications:

• It will matter more if you have a million categories than 250.
• If you want to make the search with leading wildcard, the index is not going to help much,

The countries table is doing nothing other than having a dropdown listed with all the countries. Considering there is no search function, i could leave the Id as the Index and no need for additional indexes?

For a country column, there is little reason to use a surrogate key, but the obvious selection is the country code as defined by ISO 3166.

Majority of Products are related to a CategoryId (maybe some could be null but in future i could have a Child Category that would be referenced the same way a CategoryID is at present). I have a relationship created for between the CatId in the Products table to the Id column in the Category table.

In many cases, you need indexes on foreign-key columns. Not only for searches but also support deletions. If you want to delete a category, SQL Server need to search the Products to table to see if there are products with that category. That search will be slow if there is no index and umpteen million products.

I need to search products by Name, date and description. In this instance i would create an Index for Name, date and description in this order so it first sorts by the name, then date and finally it would search the description.

That's difficult to comment on without knowing the exact business requirements. I would kind of expect a name being if not unique, but nearly unique, so it may be an overkill to add more columns to that index.

Then again, if you need to search by date, you would need an index with the Date as a the leading column.

Finally i want to be able to search a user by a range of parameters which are name, surname, email and zip code. In this instance since the order is important i could create a separate index for each column rather one index and including the 4 columns i listed?

If you need to search all these for entities separately, you will need one index per column, but these indexes could be composite indexes if this would help.

Generally, if you have a composite index on the columns (a, b, c), this index is good for searches on a, not very good for searches on b or c without a.