What are indexes? And how to add them to your Rails app?
When dealing with databases, the subject of indexing must inevitable come up. And with any rails app, you are most likely executing many searches that would benefit from indexes.
When Should We Use Them?
Before we get into what they are, we can identify some areas where they will be helpful.
Some things we do a lot:
- Rendering show and edit views — Look up a user/article/post/student attributes by a string or foreign key
- Logging in — Find a user by their email/username
- Asking about associations — Look up a project’s owner (using a join table with something other than id’s or using polymorphic associations)
All of the above procedures are going through your database row by row checking the value in the appropriate column until it comes across what it’s looking for. In SQL terms, we are looking at the portion of the query doing the heavy lifting:
A good rule of thumb is to create database indexes for everything that is referenced in the WHERE, HAVING and ORDER BY parts of your SQL queries. — Igor Šarčević of semaphoreci.com
What Are Indexes?
So we have some idea of where we need help from indexes, but what are they really?
An index for a certain column/columns in a database works similarly to an index in a book. Instead of scanning every page of a book for all instances of a subject, we flip to the index, which is usually alphabetized in some fashion, and find the subject in there. The subject entry points us to the relevant pages of the book.
A database index is an ordered data structure(read: another table)that we can create to help our database with some laborious searches. We can create an index for a certain column of our database that we query a lot (maybe an email column) and it will create another table that is, for example, alphabetized to make for a more efficient search. This table’s other column will point to the relevant row of the first table.
If you were told to look through a shuffled deck of cards to find all the Jacks you would have to look at each card until you find them... However if that deck was indexed by card value (Ace, 2, 3, … King) then you could quickly move to where the Jacks should be and find all four of them. — Stephen Ball of rakeroutes.com
Our databases use something called a b+ tree. Similar to the piles of cards, ranges of keys are split up, allowing for the database to choose which pile to go through, by checking relatively few nodes. The bottom level is where the keys connect with the value you’re looking for (in our examples this could be a row in our real table that we’re looking for).
Above we see the flow of checking through our index to find the infomration to look up on our main table. Below we can see how the actual tables might look. The clustered index table, is what we usually think of as our database table, but this isn’t how information is actually stored. We are automatically creating a clustered index for our primary key when we create a table. We can choose to add the non-clustered indexes as a way to better travers the clustered index.
Indexing does take up space and needs to be maintained as data is inserted, edited, and deleted. As we can see above, this tree is created with some information you already have in your main tables, just organized in a different way. Make sure the space and maintenance of the index is worth the efficiency in search operation.
The ways that our computers index databases and then search through those indexes is very complicated and this is just a brief overview with a lot of oversimplification. See the source links for more in depth information.
How To Add Them To Our App
The easiest way to add indexes is when you generate a migration (hence the importance of planning before we even start making our tables).
But of course, we can add them after, if we want.
Remember to choose the places that need indexes wisely. As I mentioned above, indexing does take up extra space and you don’t want to store duplicate data for no reason. One example is when you are trying to index a polymorphic association.
Do We Have To?
Indexing, at the end of the day, is about reducing time complexity in the search operations. The main reason to add an index to a database is when you search for something often. But you will see the most difference when your database is large. The databases with seed data we are working with, are likely too small to see any real results from indexing. However, for a project being seeded by an api or an app that you make accessible to the public to store data on, you should think about indexing the proper columns of your tables.
There are also some interesting situations where indexing can come in handy. One of those cases is a problem with uniqueness in our database. We can add validation to our models and this will prevent duplicate users in most cases, but what about two users trying to create the same username/email at close to the same moment in time? Validation before creation, requires that there is a length of time between the two actions, allowing for some margin of error.
Indexing the email attribute will allow our database to abort any save operation on a non-unique email, giving us a second line of defense against the wild users of your site. ActiveRecord will throw an error, and prevent us from duplicating our email in the database upon the second save attempt.
Key Takeaways
- indexes in databases work similar to indexes in books
- create them for a column(s) in your tables that you search through a lot (but not for primary id’s)
- use them for large tables
- some validations require indexing to accommodate edge cases
