Sometimes Eloquent can make a query slower

Eloquent is great, and it saves so much developer time! But occasionally a really nice-looking Eloquent query can be measurably slower than using some more basic SQL syntax.

In a recent project, I was fetching the two most recently updated records, but I only wanted results where a relationship existed and was not soft-deleted. This is really easy to express in Eloquent.

$recentlyCompleted = Todo::completed()
    ->whereHas('user')
    ->latest('completed_at')
    ->take(2)
    ->get()

select * from `todos`
where
  `completed_at` is not null
  and exists (
    select * from `users`
      where `todos`.`user_id` = `users`.`id`
        and `users`.`deleted_at` is null
      order by `name` asc
  )
  and `todos`.`deleted_at` is null
order by `completed_at` desc
limit 2

In this code snippet, you see the Eloquent query on top, and the SQL statement it runs below it. One thing of note is how the really terse whereHas('user') gets translated into that exists subquery in SQL.

Now lets take a look at a less-eloquent query, but one that performs better:

$recentlyCompleted = Todo::join('users', 'todos.user_id', '=', 'users.id')
    ->select(['todos.*'])
    ->with(['user'])
    ->whereNull('users.deleted_at')
    ->completed()
    ->latest('completed_at')
    ->take(2)
    ->get();

select `todos`.*  from `todos`
  inner join `users` on `todos`.`user_id` = `users`.`id`
where `users`.`deleted_at` is null
  and `completed_at` is not null
  and `todos`.`deleted_at` is null
order by `completed_at` desc
limit 2

By using a more explicit SQL syntax, I bypass the subquery, but more importantly, I simplified the relationship logic. Notice there's no more ordering of the user records. That global scope on the User model got applied automatically with Eloquent, but wasn't necessary in this particular case.

With this one change, the query got 30% faster. And since it was on the dashboard you see after logging in, it was going to get called a lot. There are other ways this could have been improved, but those all sound like separate tips for a future day.

It's important to note I'm not saying "never use whereHas." I continue to default to writing queries with Eloquent syntax and only deviate when something is slower than I'd like, and I'm exploring possible fixes. And when you find a place where deviating from Eloquent makes sense, add a simple comment above the query explaining why. It will save your teammate (or future you) some head-scratching 6 months from now.

Oh, and if you're wondering how to detect what is slow or diagnose why it's slow, stay tuned for some future tips on that topic.

Here to help,

Joel

P.S. Let me know if you need help with your performance issue. It's fun for me, like solving a puzzle.