I found a simple and good explanation of what database index is from Codeacademy’s article.
Database indexes are like a book’s index page and like a pointer to data in a table. It helps us to speed up querying. Indexes are necessary because we’ll want to access information that we look for in a database quickly and efficiently. By having indexes, we can shorten our time to query and retrieve data.
Often time, primary keys are stored in indexes but sometimes we want to look for data not by primary key but some other column. In such situations, it might be difficult to make those columns as unique constraint because there may be multiple rows that have the same value on that column. Instead of making them as unique constraint, we can create indexes.
The typical syntax of SQL to create an index is the following:
CREATE INDEX <index_name>
ON <table_name> (column1, column2, ...)
Indexes are used to speed up searches and queries. At the same time, updating a table with indexes takes more time than updating a table without indexes. Therefore, it’s advised to create indexes only on columns that are frequently searched.