What are some tips on tuning SQL Indexes for better performance?
If you’ve already read our article on SQL indexes, then you know that indexes are used to make queries run faster by reducing the time taken to look up data. But, the tradeoff for that improved performance is that indexes also take up space, and there must be some maintenance done on indexes to ensure that they continue to run smoothly. Let’s go over some tips, guidelines, and suggestions on how to properly use and maintain indexes to improve performance.
Don’t use too many indexes
As you know, indexes can take up a lot of space. So, having too many indexes can actually be damaging to your performance because of the space impact. For example, if you try to do an UPDATE or an INSERT on a table that has too many indexes, then there could be a big hit on performance due to the fact that all of the indexes will have to be updated as well. A general rule of thumb is to not create more than 3 or 4 indexes on a table.
Try not to include columns that are repeatedly updated in an index
If you create an index on a column that is updated very often, then that means that every time the column is updated, the index will have to be updated as well. This is done by the DBMS, of course, so that the index stays current and consistent with the columns that belong to that index. So, the number of ‘writes’ is increased two-fold – one time to update the column itself and another to update the index as well. So, you might want to consider avoiding the inclusion of columns that are frequently updated in your 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. Not only that, but the index allows some optimizers to use other methods of joining tables as well.
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. This is of course because an index on columns that are repeatedly used in predicates will help speed up your queries.
Get rid of overlapping indexes
Overlapping indexes are indexes that share the same leading column (the first column) – and that is why they are called overlapping indexes. Remember that indexes can have multiple columns. And, almost all RDBMS’s can use an index for a query, even if that query’s WHERE predicate uses only the first column of that index, and doesn’t use any of the other columns in that index. In other words, most RDBMS’s can use indexes for queries even when there is just a partial match for the query columns to the index columns. And for this reason, overlapping indexes are usually not necessary – but be sure to research this for your own particular RDBMS.
Consider deleting an index when loading huge amounts of data into a table
If you are loading a huge amount of data into a table, then you might want to think about deleting some of the indexes on the table. Then, after the data is loaded into the table, you can recreate the indexes. The reason you would want to do this is because the index will not have to be updated during the delete, which could save you a lot of time!
Ensure that the indexes you create have high selectivity
For reasons described in more detail here – index selectivity – you should create indexes with a 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.
And that’s the end of our sql index performance tips – hope they help!