Laravel computing user credit balance from transactions table slow

Question

I'm working inside a Laravel 9 project where users can purchase "credits" that are then used each time a user uses the API. However, I need to check the user's remaining quota each time, and right now my query is quite slow.

I have a CreditTransaction model, which stores each transaction in a table with a user_id and a delta column which could be positive or negative depending on whether they purchased credits, or credits were used, then I'm performing a sum of the delta column on my hasMany relationship of credit_transactions, which works, but takes a few seconds to compute when queried.

<?php

namespace App\Models;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Illuminate\Database\Eloquent\SoftDeletes;
use Laravel\Fortify\TwoFactorAuthenticatable;
use Laravel\Sanctum\HasApiTokens;

class User extends Authenticatable implements MustVerifyEmail
{
    use HasApiTokens, HasFactory, Notifiable, TwoFactorAuthenticatable, SoftDeletes;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'role',
        'first_name',
        'last_name',
        'email',
        'timezone',
        'password',
        'origin_source',
        'origin_source_other',
        'origin_campaign',
        'last_login_at',
    ];

    /**
     * The attributes that should be hidden for serialization.
     *
     * @var array<int, string>
     */
    protected $hidden = [
        'password',
        'remember_token',
        'two_factor_recovery_codes',
        'two_factor_secret',
        'origin_campaign',
    ];

    /**
     * The attributes that should be cast.
     *
     * @var array<string, string>
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
        'last_login_at' => 'datetime',
    ];

    /**
     * Get the credit transactions for the user
     *
     * @return int
     */
    public function getCreditBalanceAttribute()
    {
        try {
            if (!$this->credit_transactions) {
                return 0;
            }

            $balance = $this->credit_transactions->sum('delta');

            if ($balance <= 0) {
                return 0;
            }

            return $balance;

        } catch (\Exception $err) { }

        return 0;
    }

    /**
     * Get the credit transactions for the user
     *
     * @return array
     */
    public function credit_transactions()
    {
        return $this->hasMany(CreditTransaction::class);
    }
}

I can then perform the following to get my current balance.

Auth::user()->credit_balance

I'm not sure how best to proceed with this, as this is based on a table size of 120k rows which is quite small, equally, I need the credit balance to be accurate and fast, so caching it for 15 minutes isn't an option here.

This is my credit_transactions table:

Schema::create('credit_transactions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->index();
    $table->foreignId('credit_type_id')->default(1)->index();
    $table->foreignUuid('message_id')->nullable()->index();
    $table->integer('delta')->index();
    $table->timestamps();
    $table->softDeletes();
});

Dmitry K. · Answer 1 · 2022-12-26T16:49:26.567

Update your method to get sum of transactions to:

    /**
     * Get the credit transactions for the user
     *
     * @return int
     */
    public function getCreditBalanceAttribute()
    {
        try {
            // actually this code retrieves all user's transactions from DB 
            // and then calculate SUM

            //if (!$this->credit_transactions) {
            //    return 0;
            //}
            //$balance = $this->credit_transactions->sum('delta');
           
            // instead use this:
            // Ask database to calculate SUM of delta
            $balance = $this->credit_transactions()->sum('delta');

            if ($balance <= 0) {
                return 0;
            }

            return $balance;

        } catch (\Exception $err) { }

        return 0;
    }

See more here - Laravel Eloquent Sum of relation's column

To run sum(delta) faster you need to create a multi-column index on the user_id and delta columns.

CREATE INDEX credit_transactions_user_id_delta_idx ON credit_transactions(user_id, delta);

Or just add it to your Laravel migration:

$table->index(['user_id', 'delta']);

dbfiddle example with 20k transactions per user sum took only 7ms.

Is it possible to calculate user balance faster?
Yes, but with different approach, create a column that stores actual user balance. And after that update balance column atomically, e.g.:

begin;
-- add 500 credits to user balance
UPDATE users SET balance = balance + 500;
-- query to log user transactions
-- INSERT INTO credit_transactions (your_column_list) VALUES (your_value_list);
commit;

P.S. See also Is this SQL safe enough to handle user balance transaction?

Laravel computing user credit balance from transactions table slow

1 Answers1