SQL Indexing for Performance
Indexing is the highest-use performance tool in SQL. Here is how I think about what to index and what to leave alone. If a query is slow, the first thing I look at is indexes. The second thing I look at is indexes. The third thing I look at is indexes. Most query performance problems come down to missing indexes, wrong indexes, or indexes that exist but are not being used. Adding the right index can turn a query that takes minutes into one that takes milliseconds. Adding the wrong index can slow down every insert and update while doing nothing for your reads. Here is how I think about indexing for performance. The Cost Tradeoff An index is a data structure, usually a B-tree, that lets the database find rows without scanning the entire table. The read benefit is enormous: a lookup on an indexed column takes logarithmic time instead of linear time. The cost is that every insert, update, and delete must also update the index. More indexes mean slower writes and more storage. I treat indexes as an investment. They pay off when the read performance gain outweighs the…