How do SQL database indexes work?

An index is a data structure you can add to your SQL database that allows for quick retrieval of certain information. For example, if you have a database of names and phone numbers, an index might be created on the name column so that the database could quickly be searched for a particular student's phone number. You can think of this in the same way that a physical phone book works: Instead of searching through the entire phone book, person-by-person, you can quickly find a specific person by their name, because the phone book is ordered alphabetically. SQL indexes work in a similar way, by storing an ordered index of your records that can be quickly traversed.

Notably, a database index is separate from the actual data in the database. The data is stored in a table, while the index is a separate data structure that points to the locations of the data in the table. The most common type of index is the B-tree, which is a tree-like data structure that allows for efficient retrieval of information based on certain criteria. For example, if you wanted to find certain phone numbers in a database of people, you could use a B-tree index to find all the phone numbers of people whose names begin with a certain letter. A B-tree index is built on top of an existing table in a database. The index contains columns that correspond to the columns in the table, and each row in the index contains values for those columns that match values in the corresponding row of the table.

How do I create an index on my SQL database?

Conveniently, indexes are automatically created on columns that are part of a primary key in a table. This means that if you have an "id" column set as the primary key of a table, it will already support speedy lookups when querying for specific IDs. This is great, because in most applications, the primary key is the most common column to query against.

However, if you're frequently querying a table on a different column, you may want to create an additional index on that specific column. You can do that by running the following SQL query:

create index myindex on myschema.mytable (mycolumn);

This will create an index on the specified table and column.

When should I add a SQL database index?

With the help of SQL, you can create as many indexes as your database will allow. Common indexing scenarios will depend on the type of data you are working with. For example, if you are working with a list of customers, it might be beneficial to create an index on the customer's last name, first name, and email address. This would allow you to search for customers on these columns. If you have a list of products, you might want to create an index on the product's category and price. This would allow users to quickly search for products on your store. The possibilities are endless.

In general, it makes sense to add indexes to any columns that you plan to query on regularly. It's also important to note that indexes are particularly valuable on large tables that are already slow to query.

What are the downsides of SQL database indexes?

The main downside of indexes is that, while increasing query performance, they can decrease the performance of creating (and editing) records. This is because the database index itself needs to be updated when a new record is created, or when a value in the column being indexed is updated. Going back to the phone book example, you can see how adding a new phone number takes a little longer if you have to first find the right place to put it, compared to simply adding it to the end of the book.

For this reason, it's important to be thoughtful about where in your database to add indexes. Instead of creating them on every column, you should only add them to columns that you know are (or expect to be) regularly queried against.