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.
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:
This will create an index on the specified table and column.
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.
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.
Get to know what Basedash can do and how it changes traditional internal tools.
See a full app that connects to a Postgres database and external API made from scratch.
Ship your product faster.
Worry about internal tools less.
No credit card required.
January 20, 2023
We’ve previously explained what admin panels do; in this post we’ll talk about the most common problems they actually solve.
January 11, 2023
Learn about how to unify related datasets using SQL when a Foreign Key is and isn't present in the tables you're looking to unify.
January 3, 2023
This post is the first part of a series talking about my experience applying to and taking part in the Sumer 2020 batch of Y Combinator with my company, Basedash.
December 16, 2022
How to create a MariaDB database on DigitalOcean and connect to it via a remote server so you can access your data in Basedash.
December 9, 2022
A startup's primary focus is to build a foundation of core components that are resilient, adaptable, and can be combined to create an innovative and useful product, according to a blog post by Basedash. This is often misunderstood as being about creating a product people love.