What actually happens in a database index (and why half of them do nothing)
Same query. Same table. Same million rows. One day it takes 4 seconds . The next day, 4 milliseconds . Nothing changed in the data. The only thing that changed was one line — you added an index . Four seconds to four milliseconds is a thousand times faster, from one line of SQL. But here's the part nobody tells you: half the indexes people add do nothing. The query stays slow, the writes get…
A database index is a data structure that speeds up searching for data in a database table. However, it's a common misconception that adding an index always makes queries faster. In reality, about half of the indexes people add do nothing to improve query performance.
When a query runs without an index, the database performs a full table scan, which means it reads every single row in the table to find the matching data. This process becomes increasingly slow as the table grows. For example, searching for a user by email in a table with a million rows can take up to four seconds.
In contrast, an index is a sorted map that functions like a phone book, where each entry corresponds to a row in the table. The index is sorted based on the column(s) used in the query, allowing the database to quickly locate the desired data without scanning the entire table. For instance, if the index is created on the 'email' column, the database can find the matching email in just a few hops, rather than going through all million rows.
However, there are several ways in which an index can be ineffective or even harmful to query performance:
1. Leading wildcard: Queries that start with a wildcard (e.g., LIKE '%dev') cannot use the index efficiently, as the database has to scan the entire index.
2. Function on the column: Applying a function to the indexed column (e.g., lower(email)) prevents the index from being used, as the function changes the value.
3. Low selectivity: Indexing a column with low selectivity (e.g., a column that contains mostly true or false values) doesn't provide significant performance benefits, as the database has to read most of the index entries.
To determine whether an index will be useful, it's essential to consider the order of the indexed columns, as this determines how the data is sorted within the index. For example, indexing 'last_name' before 'first_name' allows the database to quickly locate rows with a specific last name, but indexing 'first_name' alone would not provide any benefit.
In summary, a database index is a valuable tool for improving query performance, but it's not a one-size-fits-all solution. Adding an index can significantly speed up queries, but it also comes with the cost of slower writes and increased storage. Understanding how indexes work and when to use them is crucial for optimizing database performance.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
