Indexes-- What are they?
These are similar to indexes in a book. As the index in a book helps in finding a specific topic, or a page. Indexes in database help in fetching the data easier and faster. We think that creating index improves the performance of the database query. But too many indexes may in turn reduce the performance as it may badly effect addition, updation or deletion operations. We need to be cautious in avoiding creation of unnecessary indexes.
Remember
Each index occupies some memory space. So we need to reduce the index creation to utilize space and reduce the reads required for index.When ever we create an index an index key is created. Index pages are used to create index keys. We always look for reducing number of index pages. If the size is less then the pages are less. So we need to look for creating indexes on smaller data type values rather than larger ones
Ex: Int has less size than char. Try to create indexes on columns that have int values rather than char
Guidelines
Here are some Guidelines for creating indexes.
Its always good practice to create indexes on Column/s that is used frequently in the WHERE, ORDER BY, and GROUP BY clauses.
Too many Indexes slow down the adding, deleting, and updating of rows as they accupies more space.
Index Wizard can be used to identify indexes that are not used in your queries.
Its always best to drop indexes that are not used to save the disk space.
Think and Analyze the queries performed before creating an index.
Create an index if its really needed.
Since we can have at most only one clustered index. Be cautious in choosing it.
Create clustered index that return a range of values or queries that contain group by or order by clauses.
Summary
Indexing Tips are explained
|
No responses found. Be the first to respond and make money from revenue sharing program.
|