Blazing Fast polymorphic relationship database queries in Laravel

Trick based upon reading Jonathan Reinink’s post on composite indices. This will add a set of composite indices so that you’re polymorphic relationships load significantly faster.

We want to take the techniques above and add them to our database. For instance, say we have this setup with Brand and Product. We will make the assumption that you have already added the appropriate keys in the db.

<?php

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\MorphOne;
use Illuminate\Database\Eloquent\Relations\MorphTo;

class Brand extends Model {
    public function product(): MorphTo
    {
        return $this->morphTo();
    }
}

class Toy extends Model {
    public function brand(): MorphOne
    {
        return $this->morphOne(Brand::class, 'product');
    }
}

Running this query will be extremely slow!

select * from `toys` where exists (select * from `brands` where `toys`.`id` = `brands`.`product_id` and `brands`.`product_type` =  "toy"  and `brands`.`deleted_at` is null)

Now let’s create a new migration where we add the composite keys. This will make our lookups much faster when doing the polymorphic queries.

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AlterBrandsTableAddIndexToProduct extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('brands', function (Blueprint $table) {
            $table->index(['product_type', 'product_id'], 'product_index');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('brands', function (Blueprint $table) {
            $table->dropIndex('product_index');
        });
    }
}

Now let’s run that query again…

select * from `toys` where exists (select * from `brands` where `toys`.`id` = `brands`.`product_id` and `brands`.`product_type` =  "toy"  and `brands`.`deleted_at` is null)

So much faster! Joy!