Adding indexes to your database tables is a great way to get some extra performance out of your application, especially if you have a large amount of data in your tables. They should be used sparingly and only on identified slow queries, as they have implications of their own such as increased table size and increased RAM usage. But those potential drawbacks are well worth it when you can get a query down from 3 seconds to 15 milliseconds with 5 minutes of work. The effects are particularly noticeable on polymorphic / many to many polymorphic relationships.
The type of index created depends largely on the relationship type and the content structure.
Table of Contents
Creating indexes in Laravel
Adding indexes to your tables in Laravel is very straightforward. In your migrations you can add:
See the Laravel documentation on indexes for more info.
One to one / one to many relationships
One to one and one to many relationships primarily work through foreign keys. Say we have 2 tables, users and addresses, where each user can have one address (one to one), the foreign key would be the user_id column on the addresses table. The same can be said for if a user can have multiple addresses (one to many).
For each of your foreign keys in your migration, you should let your database know that they are indeed foreign keys:
You also get some extra benefits out of this by using foreign key constraints which will ensure data integrity, i.e. making sure you don’t delete a profile which is being used by a user.
By default, MySQL using the InnoDB storage engine will add an index automatically for foreign key fields, but if you’re using a different set up, you can add one like this:
Many to many relationships
Many to many relationships require an extra table, known as a pivot table. Let’s use the example from the Laravel docs of many users having many roles, where many users could share the role of administrator. Our table set up for this example would be something like this:
As in the one to one relationship example above, we can use foreign keys to get all of the same benefits, but this time we can add two to our pivot table:
As a bonus, if you want to make sure that a duplicate can never exist in your database for a combination of the user and role, you can add a unique constraint:
Polymorphic / many to many polymorphic relationships
Indexes really come into their own on the types of queries required for polymorphic relationships. I have seen queries that previously took 30 seconds to run to go all the way down to 100 milliseconds.
Let’s start with another example. Our application this time has videos and posts, and each of them can have many comments. This is a polymorphic relationship: one post or video can have many comments. Our table set up would look like this:
Our index for this example is a bit different than the previous examples. Because almost every query we make on the comments table will include both the commentable_id and the commentable_type we need to create an index on the pair of them, this is called a compound index. To create this index we can pass two column names into the index method as an array.
The same index would apply if you were to use a many to many polymorphic relationship too, only you would apply it to your pivot table.
Order of fields in compound indexes
In terms of the performance, the order of the fields in the compound index does matter. The rule of thumb is to order them by whichever narrows down the results the most first. For example, given the following table:
| commentable_id | commentable_type |
|---|---|
| 1 | AppVideo |
| 2 | AppVideo |
| 3 | AppVideo |
| 1 | AppPost |
| 2 | AppPost |
| 3 | AppPost |
Say we want to run a query to get the comments for a Video with the id 1.
If we were to create an index using $table->index(['commentable_type', 'commentable_id']);, the index will first filter on commentable_type, which would reduce the results to 3 items:
| commentable_id | commentable_type |
|---|---|
| 1 | AppVideo |
| 2 | AppVideo |
| 3 | AppVideo |
And then by commentable_id, which would reduce our set to 1:
| commentable_id | commentable_type |
|---|---|
| 1 | AppVideo |
Conversely, if we create our index using $table->index(['commentable_id', 'commentable_type']);, the index will first filter on commentable_id, which would reduce the results to 2 items:
| commentable_id | commentable_type |
|---|---|
| 1 | AppVideo |
| 1 | AppPost |
And then by commentable_type, which would reduce our set to 1 again, getting the same result.
This contrived example demonstrates that filtering by commentable_id first makes the second filter – by commentable_type – quicker because it has fewer items to look through.
The order you choose completely depends on your content. If you’re likely to have few comments on many commentable items then it’s going to be better to filter on commentable_type first. But, as in our example, if you have few commentable items, but many comments on that item then it’s going to be better to filter on commentable_id first.
Don’t worry though, whichever you choose is going to be a performance win over not having an index at all.
If you’d like to learn more about indexes, I highly recommend the Performant Laravel
course on Servers for Hackers. It’s free and includes 3 videos on indexing in MySQL as well a bunch of other useful performance tips.
This content was originally published here.