Wherehas() and Has() drastically slow - Laravel 4.1.*

[anam-hossain]

Few days ago, some of our members complaint that in few pages website is very slow. After investigated the issue, i found that "those pages are using wherehas() and has()" for querying relationship. To see the performance of those queries, i have checked the run time of those queries using the following function:

dd(DB::getQueryLog());

Result is unpleasant. the query takes about 69 sec in my local environment and about 1 sec in the server.

My local Mac:

Processor 2.4 GHz Intel Core i7 - 4 cores
Memory 8 GB 1600 MHz DDR3

Server: hosted in a cloud with 8 cores CPU.

Relationship:

public function posts()
{
       return $this->belongsToMany('Post')->withPivot('views')->withTimestamps();
}

    $users = User::remember(5)
          ->has('posts')
          ->with('comments')
          ->orderBy('updated_at', 'DESC')
          ->take(25)
          ->get();

Output:

dd(DB::getQueryLog());

array (size=3)
      'query' => string 'select * from `users` where `users`.`deleted_at` is null and (select count(*) from `posts` inner join `comments` on `posts`.`id` = `comments`.`post_id` where `posts`.`deleted_at` is null and `comments`.`user_id` = `users`.`id` and `posts`.`deleted_at` is null) >= ? order by `updated_at` desc limit 25' (length=333)
      'bindings' => 
        array (size=1)
          0 => int 1
      'time' => float 68973.13

note: for security reason, i have changed the table names. however, i concept is same.

First, I thought that this might be the server or PHP issue. However, when i have run the raw query to the database server using "Sequel Pro", the result is same.

select * from `users` 
where `users`.`deleted_at` is null 
   and 
  (
       select count(*) from `posts` 
       inner join `comments` on `posts`.`id` = `comments`.`post_id` 
       where `posts`.`deleted_at` is null 
           and `comments`.`user_id` = `users`.`id` 
           and `posts`.`deleted_at` is null
    ) >= 1 
order by `updated_at` desc limit 25;

Time taken : 69.8 s (in my local pc)
Time taken: 945ms (in Server)

[taylorotwell]

Do you have proper indexes on the table?

[anam-hossain]

Yes, I am using indexing. if we avoid wherehas() and has() and used different technique (Join), performance increased dramatically. Average query time is now 20s - 30ms.

[taylorotwell]

Paste the alternative query? Also maybe you should just use that query instead?

[taylorotwell]

Also how many rows are in the table. It's hard for me to fathom a query hitting on those ID columns would take 70 seconds.

[anam-hossain]

Users table has more than 500K+ rows and posts tables has about 1.5 million+ rows. I will post the alternative query soon.

[yajra]

Just a thought,

Time taken : 69.8 s (in my local pc)
Time taken: 945ms (in Server)

Using your local PC, how far are you from the database server? It seem's like a network issue to me cause the server performs quite well enough. But if your database is on your local machine, then something might be really wrong and needs some optimization.

[anam-hossain]

@yajra , My database server is in my local PC. I am only concern about the server. We are using most of the Eloquent ORM features and everything working fine except has() and whereHas().

If you have more than 100K+ rows in your database, please try wherehas() and has() and share the result.

[anam-hossain]

@taylorotwell the query we have used:

SQL

select `users`.* from `users` 
inner join `comments` on `comments`.`user_id` = `users`.`id` 
inner join `posts` on `posts`.`id` = `comments`.`post_id` 
where `users`.`deleted_at` is null and comments.deleted_at is null and posts.deleted_at is null
group by users.id
order by users.`updated_at` desc 
limit 25;

Eloquent ORM:

$users = User::remember(5)
            ->with('comments')
            ->select(DB::raw('users.*'))
            ->join('comments', 'comments.user_id', '=', 'users.id')
            ->join('posts', 'posts.id', '=', 'comments.post_id')
            ->whereNull('comments.deleted_at')
            ->whereNull('posts.deleted_at')
            ->groupBy('users.id')
            ->orderBy('users.updated_at', 'DESC')
            ->take(25)
            ->get();

As i mentioned earlier, this is different technique than has(). This query run 500% faster than has() or wherehas().

[yajra]

@anam-hossain I tried running the same sql on sequel pro on my local machine and it took 291ms to complete.

Posts and Comments table have 160k records. (randomly generated)

select * from `users` 
where `users`.`deleted_at` is null 
   and 
  (
       select count(*) from `posts` 
       inner join `comments` on `posts`.`id` = `comments`.`post_id` 
       where `posts`.`deleted_at` is null 
           and `comments`.`user_id` = `users`.`id` 
           and `posts`.`deleted_at` is null
    ) >= 1 
order by `updated_at` desc limit 25;

But i was not able to run it using Eloquent.

[yajra]

@anam-hossain I think the cause of slow response is the eager loading of comments. Probably, some of the users have bulk comments and loading it at once will have an impact on performance (like in my testing data, all comments are from only 1 user with 200k comments >.<). I tried removing with('comments') and response was excellent.

$users = User::remember(5)
->has('posts')
// ->with('comments')
->orderBy('updated_at', 'DESC')
->take(25)
->get();

However, when I enabled eager loading, my local machine was not even able to complete loading of page. Also, I think you should optimized how you load the comments like on your alternative SQL where you filter the comments in relation to posts.

[anlutro]

I definitely use joins over has/whereHas for reasons like these. Would be neat if improvements could be implemented, but also know how hard it is do to dynamic joins on relationships. As for the subquery with inner join that @yajra presented, here is an idea on how to do it in Eloquent:

$model = new User; $query = $modlel->newQuery();

$subq = $model->posts()->getQuery()
    ->selectRaw('count(*)')
    ->join('comments', 'posts.id', '=', 'comments.post_id')
    ->where('comments.user_id', '=', 'users.id');

$sql = '('.$subq->toSql().') >= 1';
$query->whereRaw($sql, $subq->getBindings())
    ->orderBy('updated_at', 'desc')
    ->limit(25)
    ->get();

the $subq and whereRaw stuff can be nicely wrapped in a scope method.

[taylorotwell]

Yeah that eager loading could be the culprit. Anytime you have a table that big you're going to have to tweak your queries a bit. Just use your join work-around.

[mcordingley]

Actually, there are two problems with has that will kill database performance for large tables. The way that the sub-query is constructed means that it will be executed against the child table for each and every row in the parent table. And because this calculated result is used in the WHERE clause of the parent table, it results in a table-scan of the parent table. At this point, indexes are useless.

Both issues are mitigated when using other WHERE criteria that the database can use to filter down rows before running the sub-query, but when has appears alone or as an OR clause, performance dies.

[taylorotwell]

@mcordingley do you see anything we could do differently to improve this?