CODEWITHSUNDEEP
mysqllaravelindexingeloquentrelationship
Michael Brandl
Michael Brandl

Reputation: 41

Speeding Up / Improving Laravel Query

I'm using a Laravel Relation to relate to itself in order to get a count.

In laravel Like this: 

  public function sameak()
    {
        return $this->hasMany(self::class, 'ak', 'ak');
    }

The RAW SQL Query that it produces is: 

select
  `quotes`.*,
  (
    select
      count(*)
    from
      `quotes` as `laravel_reserved_0`
    where
      `quotes`.`ak` = `laravel_reserved_0`.`ak`
      and `laravel_reserved_0`.`deleted_at` is null
  ) as `sameaddresskey_count`
from
  `quotes`

order by
  `id` desc
limit
  25 offset 0

I'm not too familiar with Indexing however I have added an index for the ak column which greatly helped.

Is there a better way of doing this?

If someone could point me in the right direction that would be awesome.

Thanks in advance.

Mike

Upvotes: 1

Views: 79

Answers (1)

Rick James
Rick James

Reputation: 142540

where `quotes`.`ak` = `laravel_reserved_0`.`ak`
  and `laravel_reserved_0`.`deleted_at` is null

Would benefit from this "composite" (and "covering") index:

INDEX(ak, deleted_at)

Upvotes: 1

Related Questions