Laravel slow query for 30000 db rows

I have 32000 occurrence of transactions in my db, this query takes too long about 10 sec or more,

how can i optimize please

$data = DB::table('transactions')
    ->when($merchantId, function($query) use($merchantId) {
        return $query->where('user_id', $merchantId);
    })
 ->whereBetween('created_at', [
        Carbon::now()->startOfYear(),
        Carbon::now()->endOfYear(),
    ])->when($app, function($query) use($app) {
        return $query->where('application_id', $app);
    })
    ->where(function ($query) {
        $query->where('status', 'CANCELLED')
            ->orWhere('status', 'FAILED');
    })

automica

5 years ago

Level 54

@kkstar34 do you have any indexes set on your transactions table? Can you supply the migration for that table ?

1 like

Kkstar34

5 years ago

Level 1

this is the migration

Schema::create('transactions', function (Blueprint $table) {
            $table->id();
            $table->foreignId('merchant_id')->constrained('merchants');
            $table->foreignId('user_id')->constrained('users');
            $table->foreignId('application_id')->constrained('applications');
            $table->string('currency_code');
            $table->decimal('amount', 12, 2);
            $table->string('type');
            $table->foreignId('payment_method_id')->constrained('payment_methods');
            $table->boolean('is_completed')->nullable()->default(0);
            $table->boolean('is_waiting')->nullable()->default(0);
            $table->boolean('is_canceled')->nullable()->default(0);
            $table->unsignedBigInteger('card_provider_id')->nullable(); // PayPal, VISA, UBA
            $table->boolean('is_approuved')->nullable();  // Transaction is approuved by the payeur
            $table->datetime('canceled_at')->nullable(); // Canceled date
            $table->datetime('approuved_at')->nullable(); // Approbation date
            $table->string('status')->nullable();
            $table->softDeletes();
            $table->timestamps();
        });