Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

ddotgrass's avatar

Eager Loading is taking a while

Hi everyone!

I just wanted to see if this was an expected result whilst using Eager Loading. I'm having a bit of a slowness issue.

Basically, this query:

select * from `products` where `products`.`id` in ('1', '2', '3', '4', '5', '7', '8', '11', '14', '17', '18', '19', '20', '21', '22', '23', '24', '33', '35', '86', '101') and `products`.`deleted_at` is null

is fine. It doesn't take long to run at all, a few ms.

The code for it is as simple as:

$model = $this->model->with('products')->take(1500)->get();

I haven't added the pagination yet so I'm aware of that!

Anyway, another query which does the same just with less results, seems to be taking 4 seconds to load with Eager Loading, and 2 seconds without.

The slow query has quite a large IN clause, though...which I suspect is the problem.

select `product` from `accounts` where `accounts`.`product` in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99', '100', '101', '102', '103', '104', '105', '106', '107', '108', '109', '110', '111', '112') and `accounts`.`deleted_at` is null

$model = $this->model->with('accounts')->take(100)->get();

I don't know if the IN clause is simply too big or there's an underlying problem that I'm not seeing/something I should investigate.

If I remove the "->with('products')" it'll load in 2 seconds, which still seems a bit much for 100 results.

The table structure is small, I'm only selecting 6 columns.

I appreciate any insights!

0 likes
6 replies
bansal's avatar

Hi, this query select all the columns from accounts table.

   $model = $this->model->with('accounts')->take(100)->get();

You need to apply

    $model = $this->model->with([''accounts'=>function($query){
       $query->where(your_condition_if_neccessart');
         $query->select('selected_columns_name');
 }])->take(100)->get();

Laravel can optionally log in memory all queries that have been run for the current request. To check how many queries are trigged please use following method. To enable the log, you may use the enableQueryLog method

 DB::connection()->enableQueryLog();

To get an array of the executed queries, you may use the getQueryLog method:

 $queries = DB::getQueryLog();
ddotgrass's avatar

Thanks for the reply @tigana

I can achieve similar results to what you're proposing with:

$model = $this->model->with('accounts:id,name')->take(100)->get();

But it doesn't change much, it still takes about 4 seconds to execute. Whereas the first one, which loads 1,500 results, will execute in less than a second.

I'm using debug bar to debug the queries and the query I pasted above (with all the ID's in the IN clause), is the query that's being executed and subsequently, the query that's taking 4 seconds to load. I'm sure it's because it's a a large IN with about 100 bindings.

DarkRoast's avatar

Joins can be more efficient than large "IN" lists.

burlresearch's avatar
App\Account::where('product', '<=', 112)
  ->whereNull('deleted_at')
  ->get()
36864's avatar

Is the product column in the accounts table indexed?

1 like
gregrobson's avatar

I agree with @DarkRoast - even if the productcolumn is indexed large WHERE IN() clauses can prove problematic for the query parser.

Once you get to a large number of items within the IN()the query engine will mostly likely decide to do one of two things:

  1. Realise the IN() contains most of the values in the table - it will scan the whole table anyway and ignore the index.
  2. Try and seek each individual entry from that clause one a time from the index.

If you're fetching more than 10 items as part of you IN() a join is going to perform better.

1 like

Please or to participate in this conversation.