Organize Table Data with Indexes

Instructor: [00:00] Imagine going to math class at school and you're given a book that put all of the topics, such as geometry and algebra, in an unorganized manner.

[00:09] Everything was spread out and jumping between topic and topic as you read through the book. Chapters within your math book, and any book, help organize the information and can put similar groups of topics together so that it's easy to navigate and learn.

[00:23] Similar to chapters in our math books, when we insert data into our tables, we can tell our tables how to organize this data. Adding what's called an index to our tables is one of the best forms of database performance you can do. An index allows the database to go to predefined chapters, if you will, that we can define for it.

[00:44] Our users table currently has three rows of data. When we query from this table, let's say that we almost exclusively just care about individual user handles. We can create an index from this table by saying, create index test one user handle index. On the users table, let's say, user handle here. Perfect.

[01:04] Let's look at this statement again. The CREATE command does exactly as it seems. It's creating an index. Then this part is the name of what we're calling it. It can be whatever you want, but you might want to try to make it something meaningful for your personal work.

[01:18] Next, we define the table this index will live on and what sets of columns we want to organize our data by. We could pass through any number of columns here. We can also create another index on the same table that organizes data behind the scenes by create date and user handle.

[01:34] This would be great if we constantly queried out from this table where user handle equals blank and create date was between certain dates. Each database has its own way of showing what indexes are on the table. In Postgres, we just type \d and the table name.

[01:50] Here, we can see the two indexes that we've created. To remove an index, we simply use the DROP command with the name of the index we've created. That's all that's needed to remove an index. At first glance, you might be thinking, "Why not create many indexes on a table?" Although indexes are designed to enhance the database's performance, there are times when you should avoid them.

[02:12] For example, don't use indexes on small tables, tables that have frequent large batch updates or insert operations, and where columns are frequently manipulated. If you think back to our math book example, imagine now a very large math book that constantly had topics added to and removed from.

[02:29] Without organizing them in chapters, it's quick and easy to just add the info to the end of the book. However, with chapters, with each insert, we need to scan all of the indexes and make sure we put the right data in the right location. This creates a lot of overhead and can actually cause you more problems than it helps.

[02:47] Now, we could spend hours talking about indexes. There are a lot of different types, such as partial, implicit, and unique indexes. However, this is a basic and the most common example of using them. If you're interested, I'll be posting links in the video's notes for more information.