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.
September 26, 2022
Sooner or later in development work, there comes a time where you just need a flowchart. Recently we started using Mermaid, a markdown syntax supported by Notion and Github to document and share and annotate new features in-line rather than having to use a design tool or draw them out by hand.
September 21, 2022
Doing user research is difficult in and of itself, but no matter how good your are at asking the right questions, gathering data, taking insights from research, and putting that data to use, one of the most important parts of user research is finding the right users to talk to in the first place.
September 14, 2022
Product analytics tools are failing startups. At an early stage (pre-product-market fit), aggregate data is a distraction.The cure? Entity-level data.
September 1, 2022
Internal tools take time, resources, effort, and often get very little resources to build, and less to improve and grow over time. Learn how our designer, Tom Johnson, has seen and felt the pain of building internal tools over his career and how Basedash solves those issues.
August 29, 2022
Internal tool product management is identifying a need for, creating, and managing internal tools that will fulfill the needs of multiple people at your company. It's one of the most intimidating product roles in tech startups, but it doesn’t need to be.