1

I used to think that when i am using with() function with relations it is the same what join is, so for example if I have User::with('shop') it will join this second table and it is one query. Now when I turned on sql debug log I can see it is not join action, just another query without bindings.

My question is: is joining table on my own is faster than using with() ?

Andrzej Olearka
  • 19
  • 2
  • This question / similar questions have been asked twenty million and 5 times. Here's a start: https://stackoverflow.com/a/2577188/3532758 – user3532758 Sep 03 '22 at 17:26
  • `with` does a separate query. The reason why this is usually faster than a join refer to the link above – apokryfos Sep 03 '22 at 18:22

2 Answers2

2

$user->join('shop', 'users.id', '=', 'shop.user_id') generates the query below:

SELECT *
FROM `users`
INNER JOIN `shop`
   ON `users`.`id` = `shop`.`user_id`
WHERE `first_name` LIKE '%a%'

User::with('shop') this eager loading outputs the query below:

SELECT *
FROM `users`
WHERE EXISTS(
    SELECT *
    FROM `shop`
    WHERE `users`.`id` = `shop`.`user_id` AND
        `first_name` LIKE '%a%'
)

Comparison of select operation average response time between Eloquent ORM and Raw SQL

Eloquent ORM average response time
Joins Average (ms)
1 162,2
3 1002,7
4 1540,0
Raw SQL average response time
Joins Average (ms)
1 116,4
3 130,6
4 155,2

So, join query is faster than using with('shop')

matiaslauriti
  • 7,065
  • 4
  • 31
  • 43
Md Rasheduzzaman
  • 525
  • 5
  • 8
-1

Yes join works faster because with the with operator eager loading is used for the eloquent relationships. Eloquent model uses toArray() which merges own attributes and relationships in one representation, thus it performs worst.

To double check:

\DB::enableQueryLog();
$builtQuery->get()
dd(\DB::getQueryLog());
jmvcollaborator
  • 2,141
  • 1
  • 6
  • 17