In my last article we looked at some simple commands and some basic code tweaks we could use to optimize the performance of a Laravel application.

In this article, we’re going to look at how to use database indexes to improve the performance of queries so that your Laravel app doesn’t become sluggish when you start to query lots of data. I was able to improve my performance by 100% in the example in this post but your mileage will almost certainly vary.

What is a Database Index?

If you’ve never thought about database indexing before, you might be surprised to learn that you have probably been using database indexes for quite some time. If you’ve ever created a table in MySQL with a primary key column (normally called id) MySQL has actually created an index for that column (called PRIMARY by default). If you happen to have read Iain’s previous post on SQL Query Optimization for faster WordPress sites then you will also have a decent idea of what database indexes are already.

A database index improves the speed of retrieving data from a table by maintaining an “index” of the data. Think of it like an index of a book but for your database table. The index is used to quickly find the correct row or rows of data without having to scan the whole table every time. For small databases, the performance gains are minimal as querying small databases is so fast anyway. However, once a table starts to grow in size (think millions of rows) you will start to notice a considerable impact on performance when querying the table without indexes.

There are some tradeoffs to consider when using indexes. An index uses storage space but the space it uses is normally minimal compared to the size of the database. However, for small tables having an index might actually make MySQL work harder because it has to process the index when running the query and write queries will get slower because it has to rebuild the index. So it’s advised to only add indexes once a table starts to grow in size.

Diagnosing an Index Problem

In my last article we created a Laravel app with a Task model and a simple relationship that a user can have many tasks. Continuing with this example, let’s imagine a user has ~2.4 million tasks and we want to fetch a user’s tasks. We might do something like this:

$user = User::find(1);
$tasks = $user->tasks()->get();

The first thing you’ll probably notice is that the page won’t load because PHP will run out of memory (e.g. “Allowed memory size of X bytes exhausted”). So to begin to diagnose this issue we’ll need to jump over to a database management app (I’m using TablePlus at the moment). Let’s run the raw SQL query that would be generated by Laravel:

SELECT * FROM `tasks` WHERE `tasks`.`user_id` = 1 AND `tasks`.`user_id` IS NOT NULL

Running this query takes ~1 sec on my machine. Not great.

To help us debug the issue with this query we can use the MySQL EXPLAIN statement to help us understand what is happening behind the scenes. Simply run the query again but add explain to the beginning of the query:

EXPLAIN SELECT * FROM `tasks` WHERE `tasks`.`user_id` = 1 AND `tasks`.`user_id` IS NOT NULL

Running this query will give us a bunch of information, but the important fields to look for are the possible_keys and key fields. These will tell us which indexes (keys) are available for this query and which index is actually used. It’s also important to note that the rows field tells us how many rows were scanned during this query. So when I run this for our tasks query I get the following results:

Notice that, in this case, no indexes were being used and we had to scan through all ~2.4 million rows! No wonder the query takes so long.

Adding a Database Index

Now that we have diagnosed the problem, let’s go ahead an add an index to the user_id column for the tasks table. Normally you would do this in Laravel by creating a new migration and adding an index to the table using the $table->index() method:

Schema::table('tasks', function (Blueprint $table) {
    $table->index('user_id');
});

After running the migration to add the index, let’s run our query again and see how long it takes. On my machine, the query now takes ~500 ms. That’s half the time it took before! If we run the EXPLAIN statement again, let’s see what’s happening behind the scenes:

We can see that the query is now using our new tasks_user_id_index that we just created and by using this new index the number of rows it has to scan has dropped from ~2.4 million to ~400,000. Success!

It’s worth noting at this point that your mileage may vary when adding indexes to database tables as the performance gain will depend on not only the amount of data in the table but also the type of query being run. As a side note, we could have just added user_id as a foreign key to the table. Foreign keys automatically index the column plus they enforce data integrity.

As you might expect, there is a bit of an art to adding database indexes. For example, if you have multiple WHERE clauses in your query it might make sense to add a multi-column (compound) index which includes every field in your WHERE clause. Say our query is:

SELECT * FROM `tasks` WHERE `tasks`.`user_id` = 1 AND `tasks`.`created_at` >= '2019-03-19 11:00:00'

We could add a compound index like this:

Schema::table('tasks', function (Blueprint $table) {
    $table->index(['user_id', 'created_at']);
});

Running the above query with the compound index improved the performance of the query on my machine by about 50% again.

However, be aware:

• Adding more indexes will increase the size of your database and the RAM usage of your database (as MySQL tries to store indexes in memory).
• MySQL is smart enough to use indexes even though the index fields might not include every field in your WHERE clause. If I added both indexes above, an EXPLAIN would show the single user_id index would be used instead of the compound index, even though I had multiple WHERE clauses in my query.
• MySQL can use multiple indexes if it needs to.
• The order of the fields in a compound index can also have an impact. Generally, they should be ordered most common to least common.

So you might still see a performance gain without adding more compound indexes. Using EXPLAIN is your friend here to find out what MySQL does behind the scenes.

Next Time

Now that you have a basic understanding of why database indexing is important for performance, and how to use them in your Laravel applications, you should be able to start implementing them in your databases if you haven’t done so already. There is a bit of an art to adding the right indexes but, as with most things, practice makes perfect.

In my next article, we’re going to look at how we can further improve the performance of Laravel by using object caching.

Have you ever used database indexing in Laravel before? Got any database indexing tips to share? Let us know in the comments.