What is Database Index?
A database index is a database structure which is often utilized to make searches or data retrieval faster. Imagine trying to find all matching records with regards to a column filter in a large database. If we know there is only one unique match, then we can stop immediately after finding it. However, if there are multiple matches, we cannot stop until we finish scanning every record, which can be a resource draining action if we are dealing with a large table. However, if we have a “helper” column which marks each record uniquely, we can potentially split the search by performing the search in parallel on different pieces, or by using some other algorithm to speed up the process.
How does Database Index work?
Adding indexes does make the table bigger. Therefore, it is not practical to index every field. In most database tools, primary keys are indexed. It is also quite common for foreign keys to be indexed as foreign keys are often used for data retrieval.