Optimizing your database with indexes
In short, the author finally figures out that using an index can decrease the execution time of a query dramatically. An incredibly uninformative article, since there is no mention of what he indexed or any advice as how you should go about doing it yourself.
Well being a database guy at heart I thought that I would give some hints in this subject since it’s a fairly common one. Many a times in my career I’ve been asked by people about what they should be indexing in their databases. I don’t know why, but people seem scared to place an index on a table. Let me just tell you that you aren’t going to destroy your database by experimenting with indexes, you might slow it down a bit, but you won’t mess up any data. So don’t be afraid to start playing around.
So what should we index? As a rule of thumb you should be indexing the following:
- Any foreign keys linking table together (you will be amazed that this is the most overlooked index to create)
- Any columns that you will use to create joins between table (usually these are the same as the foreign keys, but they could differ sometimes)
- Any columns that you will be filtering against in your queries (these are columns in your queries that you reference in your where clauses)
By going down those 3 items, you will most likely see a performance gain in your database. Now there are some gotchas with this:
- Be careful to not index a column more then once. This could be common if you place an index on a foreign key and then include that foreign key within another index. There are obviously exceptions to the rule.
- You don’t want to place an index on every column in a table even if you do use even column in where clauses throughout your application. By doing so you will be killing the performance of the table. Try to use judgment in selecting the most important columns within the table.
Can you have too many indexes on a table? Of course you can! One of the ways that you know that you have too many indexes is if you see a lag when altering data on a table (inserting, updating and deleting). Remember that your database server has to maintain and do housecleaning on all indexes affected by altering the data on a table. Because of this, if you place too many indexes on a table, it will slow down the write and updates of the indexes which will cause lags.
For people just getting started with indexes and using SQL Server, a great way to learn and practice is to use the Index Tuning Wizard that is included with SQL Server. To practice, place a slow running query or a query that you want to optimize in Query Analyzer and execute it. Next, try figuring out on your own which columns on which tables you should index in order to make the query perfomrm better and write them down. Run the Index Tuning Wizard and see if your suggestions matches the ones that the Index Tuning Wizard comes up with.
Good luck optimizing your tables with indexes. If you have any questions, suggestions or comments, leave them below.