Question

List 6 factors you should consider when designing indexes. Please explain your answers.

Answer 1

Some of the main factors used to design Indexes are:

1. Create indexes for Uniqueness as it is required when unique constraints and primary key constraints do exist in a table.

2. Avoid over-indexing tables as it will affect the performance of INSERT, UPDATE and DELETE statements.

3. Index by the workload(Usage) and not by the table as simply guessing the indexes might be a bit confusing in future usage rather than just keeping in mind to optimize the access of your SQL queries.

4. One can index the Columns used in foreign keys as they are mostly used in join operations.

5. Do consider using clustered Index for Large Tables and Range-of-Values Queries as it determines the storage order of the records on disk.

6. Data modification implications should be kept in mind as Updating a table using INSERT, DELETE, UPDATE queries might also bring changes to the indexes associated with the table and not just the tables record.

These are some of the main points one can keep in mind while using INDEXES on your table.