CODEWITHSUNDEEP
phpmysqllaravel
underface
underface

Reputation: 45

speed up query laravel [solve]

I need some help. In my DB i have 2 tables

My DB have 2 table's contracts (around 2 500 000 records) and customers (around 1 500 000 records). Relationship between 2 tables/2 models.

class Contract extends Model
{
    public function customer()
    {
        return $this->belongsTo('App\Customer');
    }
}


class Customer extends Model
{
    public function contracts()
    {
        return $this->hasMany('App\Contract');
    }
}

With some help community Stack I was built this function

$customers = Customer::with(['contracts' => function($query)
       {
          $query->where('pos', 'Ropczyce')
                ->where('typ', 'U')
                ->where('data_end','>=','2017-07-01')
                ->where('data_end','<=','2017-08-31');
       }
      ])->whereHas('contracts', function ($query) {
          $query->where('pos', 'Ropczyce')
                ->where('typ', 'U')
                ->where('data_end','>=','2017-07-01')
                ->where('data_end','<=','2017-08-31');
      })
      ->paginate(20);

But generate data spent a lot of time (around 6-7 sec). I imagine in this moment, function working on the all customers and searching contracts where query is true. in my opinion this is a problem of the.

I searching some solution for save the time. searching only contracts spent around 1 seconds.

$contracts = Contract::where('pos', 'Ropczyce')
                    ->where('typ', 'U')
                    ->where('data_end','>=','2017-07-01')
                    ->where('data_end','<=','2017-08-31')
          ->with('customer')->paginate(20);

I care that the data is built on this principle. Client1 - contact1, contract2 ... Client2 - contact1, contract2 ...

e.t.c

code of table contract

public function up()
    {
        Schema::create('contracts', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('customer_id')->unsigned();
            $table->char('phone_number',9);
            $table->date('data_start');
            $table->date('data_end');
            $table->char('contract_number',90);
            $table->char('pos',10);
            $table->char('typ',1);
            $table->char('weryfikacja',1);
            $table->date('data_weryfikacji');
        });
    }

Upvotes: 2

Views: 1880

Answers (1)

Jerodev
Jerodev

Reputation: 33186

You should add a composite index to your database for these filters, this will make the query much faster.

Schema::create('contracts', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('customer_id')->unsigned();
    $table->char('phone_number',9);
    $table->date('data_start');
    $table->date('data_end');
    $table->char('contract_number',90);
    $table->char('pos',10);
    $table->char('typ',1);
    $table->char('weryfikacja',1);
    $table->date('data_weryfikacji');

    // Composite index for faster filtering on these fields.
    $table->index(['pos', 'typ', 'date_end']); 
});

Upvotes: 2

Related Questions