Indexes: Useful, Useless and Composite

Now, let's talk about indexing the database columns, what impact it has, how to do that, and let's measure the performance.

Initial Project Structure

Imagine that you have some log of visitors to websites. You have a table of websites. I have seeded 100 rows.

Then, a table of visitors with IP address, country, user agent, page URL, and random dates was created at the field.

In the Controller, we take the country, for example, from the request and group visitors from that country by URL, counting the number of visitors per URL over some time period.

use App\Models\Visitor;
use Illuminate\Contracts\View\View;
 
class HomeController extends Controller
{
    public function __invoke(): View
    {
        $visitors = Visitor::selectRaw('websites.url, count(*) as visitors')
            ->where('country', request('country'))
            ->whereBetween('visitors.created_at', [
                now()->subDays(60)->startOfDay()->toDateTimeString(),
                now()->subDays(30)->endOfDay()->toDateTimeString(),
            ])
            ->join('websites', 'visitors.website_id', '=', 'websites.id')
            ->groupBy('website_id')
            ->orderByDesc('visitors')
            ->get();
 
        return view('home', compact('visitors'));
    }
}

Which gives results similar to the below:

In the debugbar, we can see that the query time is about 24ms, and the whole page is 128ms.

Now, what is the index, and how can we apply it here?

Applying Index

Indexes help query or filter one or more columns. In this case, we have a where condition by country, which is a varchar text field.

If we add an index to that country field, it means that in the database, it would be sorted differently, separately as an index, and would run faster. So, the WHERE condition would filter faster.

Let's try it out.

You can create an index in the Migration.

database/migrations/xxx_add_index_to_country_in_visitors_table.php:

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

After running the migrations and a few refreshes, the SQL query is down to about 2ms, and the whole page loads to sub 100ms.

But indexes have their downsides as well. Data inserting becomes a bit slower because the database management system needs to insert not only the data but also the index.

Also, then the size of the table becomes a bit bigger depending on the different values of the field.

Different Example: "Useless" Index?

Another example is from the same structure but from a different query. We query the website visitors where website_id equals something and group by visitor_date.

use App\Models\Visitor;
use Illuminate\Contracts\View\View;
 
class HomeController extends Controller
{
    public function __invoke(): View
    {
        $visitors = Visitor::selectRaw('date(created_at) as visitor_date, count(*) as visitors')
            ->where('website_id', request('website_id'))
            ->whereBetween('visitors.created_at', [
                now()->subDays(60)->startOfDay()->toDateTimeString(),
                now()->subDays(30)->endOfDay()->toDateTimeString(),
            ])
            ->groupBy('visitor_date')
            ->orderBy('visitor_date')
            ->get();
 
        return view('home', compact('visitors'));
    }
}

Which gives results similar to the below:

And the query, in this case, is really fast.

When you create a foreign key in the Migrations, Laravel automatically adds an index. That's another reason to create foreign keys: they are automatically indexed.

Schema::create('visitors', function (Blueprint $table) {
    $table->id();
    $table->foreignId('website_id')->constrained(); 
    $table->string('ip_address');
    $table->string('country');
    $table->string('user_agent');
    $table->string('page_url');
    $table->timestamps();
});

You can see the indexes in your GUI program.

Also, with this example, let's take a look at created_at. Maybe we can speed up even more if we do the index on created_at. Then, that where between would run faster because the actual query is grouping the visitor where website_id equals x and then created_at between.

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

The query time is almost the same.

But lets look at the database size differences before and after index for the created_at field. After adding the index the database size increased by 8MB.

It may depend more on the number of records in your database table and may slow down the data insertion for your case.

So, when adding the indexes, you need to be careful because they speed up the whole query. But you need to add indexes only for those fields by which you query, which you filter queries by, or which are the most used queries. Then, it's worth adding an index there.

Composite Index

Let's take a look at the last example. If we get back to the same first example filtering by country and created_at, you may create an index for multiple fields, a so-called composite index. It would create an index for ordering or where conditions on two fields.

In the Migration, you can pass an array when you create an index.

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

In this case, we have an index on the conditions that we want to query, such as country and created_at.

So, these are examples of indexes.

But, again, be careful. Only create indexes on some of the columns that are heavily used in where/order clauses. Also, you may create a combined index if you have such repeating conditions on multiple pages.