Don’t use too many indexes
Try not to include columns that are repeatedly updated in an index
Creating indexes on foreign key column(s) can improve performance
Because joins are often done between primary and foreign key pairs, having an index on a foreign key column can really improve the join performance.
Create indexes for columns that are repeatedly used in predicates of your SQL queries
Take a look at your queries and see which columns are used frequently in the WHERE predicate. If those columns are not part of an index already, then you should add them to an index.
Consider deleting an index when loading huge amounts of data into a table
Then, after the data is loaded into the table, you can recreate the indexes.
Ensure that the indexes you create have high selectivity
A general rule of thumb is that indexes should have a selectivity that’s higher than .33. Remember that indexes that are completely unique have a selectivity of 1.0, which is the highest possible selectivity value.
Read full article from SQL Index Performance
Try not to include columns that are repeatedly updated in an index
Creating indexes on foreign key column(s) can improve performance
Because joins are often done between primary and foreign key pairs, having an index on a foreign key column can really improve the join performance.
Create indexes for columns that are repeatedly used in predicates of your SQL queries
Take a look at your queries and see which columns are used frequently in the WHERE predicate. If those columns are not part of an index already, then you should add them to an index.
Consider deleting an index when loading huge amounts of data into a table
Then, after the data is loaded into the table, you can recreate the indexes.
Ensure that the indexes you create have high selectivity
A general rule of thumb is that indexes should have a selectivity that’s higher than .33. Remember that indexes that are completely unique have a selectivity of 1.0, which is the highest possible selectivity value.
Read full article from SQL Index Performance
No comments:
Post a Comment