Indexing - rFronteddu/general_wiki GitHub Wiki
One of the first things to do when DB performance is no longer satisfactory is DB indexing. The goal of an index is to make a table faster to search through and find the row or rows that we want. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient access of ordered records.
For examples, you could index books by title and author to provide fast lookup for both. An index is a data structure that can be perceived as a table of contents that points to the location where actual data lives. So when we create an index on a column of a table, we store that column and a pointer to the whole row in the index.
When adding rows or making updates to existing rows for a table with an active index, we not only have to write the data but also have to update the index. This will decrease the write performance. This performance degradation applies to all insert, update, and delete operations for the table. For this reason, adding unnecessary indexes on tables should be avoided and index that are no longer used should be removed.