In databases, how do you determine whether making an index on one particular column is good, useless, or bad?
In: Technology
Generally you can tell if an index is required on a column/set of columns because the queries frequently make use of those columns as criteria for SELECT/UPDATE/DELETE, JOINs, GROUPing, ORDERing or PARTITIONing, and it is a column with diverse values (eg. a boolean flag column doesn’t need indexes). In those cases, indexes are almost certainly going to be good. If the columns aren’t used that way, then the index is useless – which also means bad, because it means unnecessarily wasting resources (storage, RAM, CPU). Indexes are very rarely directly make query performance worse, if one isn’t necessary it simply won’t be used.
If you need more quantitative data than ‘educated guess’, then the query execution plan will give some idea. If your query is doing full table scans at some point, then it probably needs indexes there. The planner will tell you what the cost of each operation is, and if it decreases after you’ve added the index, then congrats you’ve improved performance. You will also want to do some real-world query executions and measure the actual impact to execution time and resource utilisation before and after adding the index.
Of course you can only prove a performance increase after adding the index, which may not be an easy option in a live production environment if you don’t have a suitable test environment that closely resembles prod. In that case you just need to rely on experience and probability. DB tuning often requires some trial and error.
Latest Answers