Eloquent Performance: Enum VS Int/Tinyint and SoftDeletes

I recently worked with an Eloquent query with the where status = 'new' filter by ENUM value, which felt quite slow. I experimented with changing it to status_id instead as a big/tiny integer with a foreign key. Was it faster? Let's find out together.

At the end of this tutorial, you'll find the link to the GitHub repository, so you can play around and experiment, too.

The Scenario/Problem

The situation comes from the official Filament demo, where I seeded 1M orders and had a menu badge for counting the new orders:

That executed this SQL query under the hood:

select count(*) as aggregate
  from `shop_orders`
  where `status` = 'new'
    and `shop_orders`.`deleted_at` is null

The column status was created as ENUM on the DB level:

Schema::create('shop_orders', function (Blueprint $table) {
    $table->id();
 
    // ... other fields
 
    $table->enum('status', [ 
        'new', 'processing', 'shipped', 'delivered', 'cancelled' 
    ])->default('new'); 
 
    // ... other fields
 
    $table->timestamps();
    $table->softDeletes();
});

According to the Laravel Debugbar, the average execution time was around 150 ms.

Not very bad, but my initial reaction was that it could be faster to search where status_id = 1 or something.

So, here's my experiment.

Step 1. New Tables: Migrations

First, a few Artisan commands to create separate models/migrations:

php artisan make:model Status -m
php artisan make:model TinyStatus -m

And then, structure with the initial data seeded right away in migrations:

use App\Models\Status;
 
// ...
 
return new class extends Migration
{
    public function up(): void
    {
        Schema::create('statuses', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
 
        Status::create(['name' => 'new']);
        Status::create(['name' => 'processing']);
        Status::create(['name' => 'shipped']);
        Status::create(['name' => 'delivered']);
        Status::create(['name' => 'cancelled']);
    }
 
};

Separately, the same for Tiny Statuses:

use App\Models\TinyStatus;
 
// ...
 
return new class extends Migration
{
    public function up(): void
    {
        Schema::create('tiny_statuses', function (Blueprint $table) {
            $table->tinyIncrements('id');
            $table->string('name');
            $table->timestamps();
        });
 
        TinyStatus::create(['name' => 'new']);
        TinyStatus::create(['name' => 'processing']);
        TinyStatus::create(['name' => 'shipped']);
        TinyStatus::create(['name' => 'delivered']);
        TinyStatus::create(['name' => 'cancelled']);
    }
};

By the way, did you know about the ->tinyIncrements() method? It creates a TINYINT column as an auto-incremented primary key instead of a default BIGINT generated by $table->id().

Notice: I also added the name column in both Models into a $fillable array.

Step 2. Two New Columns with Foreign Keys

Now, let's create two columns in the shop_orders table: one foreign key to the statuses table and another one on the tiny_statuses table:

php artisan make:migration add_columns_to_shop_orders_table

Here's the Migration code:

public function up(): void
{
    Schema::table('shop_orders', function (Blueprint $table) {
        $table->foreignId('status_id')->default(1)->constrained();
 
        $table->unsignedTinyInteger('tiny_status_id')->default(1);
        $table->foreign('tiny_status_id')->references('id')->on('tiny_statuses');
    });
 
    $sqlStatus = "UPDATE shop_orders set status_id =
        case when status='new' then 1
            when status='processing' then 2
            when status='shipped' then 3
            when status='delivered' then 4
            when status='cancelled' then 5
            else 5
        end";
    \Illuminate\Support\Facades\DB::statement($sqlStatus);
 
    $sqlTinyStatus = 'UPDATE shop_orders set tiny_status_id=status_id';
    \Illuminate\Support\Facades\DB::statement($sqlTinyStatus);
}

As you can see, we immediately set the values of the new columns with SQL statements.

Side note: it may not be ideal to execute SQL and Eloquent statements directly in migrations, but I'm doing it simply to avoid extra paragraphs of text about seeding and explaining how to launch it.

As a result, we have this in the database:

We're ready to use those columns and measure the improvement results.

Step 3. Change ->where() Condition: Is It Faster?

Now, the place where I had this condition is in the OrderResource of Filament.

app/Filament/Resources/OrderResource.php

class OrderResource extends Resource
{
    // ...
 
    public static function getNavigationBadge(): ?string
    {
        /** @var class-string<Model> $modelClass */
        $modelClass = static::$model;
 
        return (string) $modelClass::where('status', 'new')->count();
    }
 
    // ...
 
}

Let's change it to use status_id.

return (string) $modelClass::where('status', 'new')->count(); 
 
return (string) $modelClass::where('status_id', 1)->count(); 

Refreshing the page to see if it is faster than the previous 150ms...

Wait, WHAT?! 327ms? So, it's 2x slower? Relaunching the page just to double-check...

It's a bit better with 281ms, but still slower. So, it wasn't a coincidence :(

Ok, maybe it's because of the BIGINT? Let's try the tiny_status_id.

return (string) $modelClass::where('status_id', 1)->count(); 
 
return (string) $modelClass::where('tiny_status_id', 1)->count(); 

That's even weirder. So, 289ms means no improvement with having a smaller field? What the...?

Just to make sure, I ran a test with Benchmark Laravel class and 10 iterations:

use App\Models\Shop\Order;
use Illuminate\Support\Facades\Route;
use Illuminate\Support\Benchmark;
 
Route::get('benchmark', function() {
    Benchmark::dd([
        "Enum" => fn() => Order::where("status", "new")->count(),
        "Bigint" => fn() => Order::where("status_id", 1)->count(),
        "Tinyint" => fn() => Order::where("tiny_status_id", 1)->count()
    ], 10);
});

Results may be unexpected, but pretty clear:

array:3 [▼ // vendor/laravel/framework/src/Illuminate/Support/Benchmark.php:67
  "Enum" => "181.001ms"
  "Bigint" => "270.180ms"
  "Tinyint" => "258.069ms"
]

Mmmkay... but... why enums are faster?

EXPLAIN: Are Indexes Being Used?

The first thought on DB performance is about indexes, right? Do we have/use them?

By default, Laravel adds...