Database indexing is a performance technique that is used when complex queries are slow. When creating an index table, you are physically creating a copy of a table, where the data is stored in some kind of order, and Symlinked to the original table. Think about an index page at the end of the with all definitions and page numbers at the end of the course book. Database indexing is very similar to it.
Types of Indexes
You can choose different types of indexes. A Single-column index allows you to select one column in a database, you can also compose index out of multiple columns, which is called Multi-column index.
When to use indexing
In general Premature optimisation should be avoided most of the time, but there are cases where developers should apply indexes by default
- Primary keys - querying by primary key will be the most common, so that would be the best thing to start with
- Foreign keys - indexing those speed up the read queries. A rule of thumb should be foreign keys that are not unique. Some databases index foreign keys by default.
- Anything with WHERE Clause or ORDER BY as this can speed up the query by quite a bit. This can be achieved using Multi-column index.
A good indicator whether or not you should use indexing is CPU and memory spikes during a full scan.
Considerations
There are a few things that you should consider when using indexing