Database indexing in Laravel - Everything you need to know

#laravel #php #mysql

Imagine this

You're a high school student and you're assigned a project in which you need calculate the mass of the Earth and you're not allowed to use the internet. The only resource available to you is the school's old library; plus you need to hand in the results in 2 hours!

So you head to the library as fast as you can to start working on your project. As you organize your desk an idea pops into your head that can easily find the answer you're looking for; but in order to use that idea you need one complicated math formula which you forgot.
So what do you do? Of course! You start looking for that formula in the books!

Since it's an old library and there are no indexes to use you start from the very first aisle an go through each book page by page!
After a couple hours you find the formula you were looking for. Great! But wait a second. You look at your wristwatch and realize you're out of time.

What a shame.

If only the library had indexes for each aisle and each book you would've found the formula much faster and would've got an A on the project!

Point of the stroy

The point of the story is that when there are no indexes on the database you data retrieval can take a LOT of time and this will result in users leaving your website/application.

You might be wondering how exactly database indexes are related to book indexes?

Well, did you know that the idea of database indexing came from book indexing? and did you know that database indexes work exactly like book indexes?

There's the answer you were looking form

So what is a database index?

Let's ask wikipedia

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed.

Now that we know what an index is, how can we use it in our database?

As always there are a couple ways to do this:

Way no. 1 - Using migration files

This is probably is easiest way to add an index inside a table. The only thing you need to do it to add the code inside your migration file and then migrate the table. That's it.

Let's have a look.

Schema::create('comments', function (Blueprint $table) {
    $table->increments('id');

    //column we want as an index
    $table->integer('post_id')->unsigned();
    //defining the index
    $table->foreign('post_id')->references('id')->on('posts')
        ->onDelete('cascade');

    $table->string("comment");
    $table->timestamps();
});

Notice that the foreign key post_id corresponds to column's name.

But that's not all of it. Behind the scenes, Laravel changes a few details so if you log into your MySQL you'll find out that the index name is not what you set in the migration file.
Laravel changes your foreign key name to comments_post_id_foreign for more clarity but there's a pattern to it:
(NameOfTheTable + NameOfTheColumn + 'foreign')

Way no. 2 - Using SQL

What if you're application is in production and you're scared you might break something with migration? or maybe you have already migrated the table? If these are the cases then this way is the way to go.

What you need to do is simply log into your MySQL database and run the following command:

ALTER TABLE `comments` ADD CONSTRAINT `comments_post_id_foreign` FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

Notice that we are following Laravel's footsteps in naming our index.

That's it.

By using this simple technique you can make huge difference in your application performance; but be aware that too many indexes can do more harm than good. (It can drastically slow down insert operation; more on this here.)

There might be a few more ways that I'm not familiar with. If you know any other way you're more than welcome to share it with us.

Until the next post, take care. 🖤