How are indexes created?

In a database, data is stored in rows which are organized into tables. Each row has a unique key which distinguishes it from all other rows and those keys are stored in an index for quick retrieval.

Since keys are stored in indexes, each time a new row with a unique key is added, the index is automatically updated. However, sometimes we need to be able to quickly lookup data that is not stored as a key. For example, we may need to quickly lookup customers by telephone number. It would not be a good idea to use a unique constraint because we can have multiple customers with the same phone number. In these cases, we can create our own indexes.

The syntax for creating an index will vary depending on the database. However, the syntax typically includes aCREATEkeyword followed by theINDEXkeyword and the name we’d like to use for the index. Next should come theONkeyword followed by the name of the table that has the data we’d like to quickly access. Finally, the last part of the statement should be the name(s) of the columns to be indexed.

CREATE INDEX <index_name>ON <table_name> (column1, column2, ...)
Copy to clipboard

CREATE INDEX <index_name>ON <table_name> (column1, column2, ...)

For example, if we would like to index phone numbers from acustomerstable, we could use the following statement:

CREATE INDEX customers_by_phoneON customers (phone_number)
Copy to clipboard

CREATE INDEX customers_by_phoneON customers (phone_number)

The users cannot see the indexes, they are just used to speed up searches/queries.

Note:Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.

Summary

This article was designed to give you an overview of database indexes, including how:

• Indexes are a powerful tool used in the background of a database to speed up querying.
• Indexes contain all the necessary information needed to access items quickly and efficiently.
• Indexes serve as lookup tables to efficiently store data for quicker retrieval.
• Table keys are stored in indexes.
• Indexes for non-key values can be created with aCREATE INDEXstatement.

Happy indexing!

*We will not be using indexes in this course, but it’s good to know about them. 👩‍🏫

'The Codecademy Team, composed of experienced educators and tech experts, is dedicated to making tech skills accessible to all. We empower learners worldwide with expert-reviewed content that develops and enhances the technical skills needed to advance and succeed in their careers.'