[8.x] Improve one-of-many performance by cbl · Pull Request #37451 · laravel/framework

cbl · May 21, 2021

Background

Eager loading one-of-many relationships can become slow for big tables.

Solution

When eager loading, the related models are loaded using WHERE foreign_id in (1,2,3,...), currently this constraint is applied to the parent query in one of many relationships:

SELECT *
FROM `logins`
INNER JOIN (
    SELECT MAX(id) AS id
    FROM logins
    GROUP BY logins.user_id
) AS latest_login 
ON latest_login.id = logins.id
WHERE user_id in (1,2,3,4,5) # <---

This means that the subselect query gets MAX(id) rows for every group in the table, not only the required ones.

This can be improved by adding the constraint to the subquery:

SELECT *
FROM `logins`
INNER JOIN (
    SELECT MAX(id) AS id
    FROM logins
    WHERE user_id in (1,2,3,4,5) # <---
    GROUP BY logins.user_id
) AS latest_login 
ON latest_login.id = logins.id

See the `EXPLAIN ANALYZE` results for both queries for more information...

Results for 4,000 users and 100,000 logins.

Filter rows on parent query:

-> Nested loop inner join  (cost=50420.32 rows=500500) (actual time=20.806..21.036 rows=5 loops=1)
    -> Index range scan on logins using logins_user_id_index, with index condition: (logins.user_id in (1,2,3,4,5))  (cost=57.51 rows=125) (actual time=0.048..0.190 rows=125 loops=1)
    -> Index lookup on latest_login using <auto_key0> (id=logins.id)  (actual time=0.001..0.001 rows=0 loops=125)
        -> Materialize  (cost=1650.35..1650.35 rows=4004) (actual time=0.167..0.167 rows=0 loops=125)
            -> Index range scan on logins using index_for_group_by(logins_user_id_index)  (cost=1249.95 rows=4004) (actual time=0.012..17.431 rows=4000 loops=1)

Filter rows on subquery:

-> Nested loop inner join  (cost=60.31 rows=125) (actual time=0.121..0.130 rows=5 loops=1)
    -> Filter: (latest_login.id is not null)  (cost=0.13..16.56 rows=125) (actual time=0.113..0.115 rows=5 loops=1)
        -> Table scan on latest_login  (cost=2.50..2.50 rows=0) (actual time=0.000..0.001 rows=5 loops=1)
            -> Materialize  (cost=2.50..2.50 rows=0) (actual time=0.112..0.114 rows=5 loops=1)
                -> Group aggregate: max(logins.id)  (actual time=0.041..0.100 rows=5 loops=1)
                    -> Filter: (logins.user_id in (1,2,3,4,5))  (cost=25.32 rows=125) (actual time=0.021..0.082 rows=125 loops=1)
                        -> Index range scan on logins using logins_user_id_index  (cost=25.32 rows=125) (actual time=0.019..0.063 rows=125 loops=1)
    -> Single-row index lookup on logins using PRIMARY (id=latest_login.id)  (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=5)

How It Works

The subquery is bound to the class property $oneOfManySubQuery, the constraints to restrict rows by the foreign_key when eager loading or retrieving a single result will be added to this sub query builder. The subquery will be added to the inner join using beforeQuery introduced in #37431

More details...

A new public method is added to retrieve the one of many subquery builder instance:

framework/src/Illuminate/Contracts/Database/Eloquent/SupportsPartialRelations.php

Line 29 in e16066d

public function getOneOfManySubQuery();

The getRestrictionQuery method decides which query foreign key constraints should be added to:

framework/src/Illuminate/Database/Eloquent/Relations/Relation.php

Lines 374 to 377 in e16066d

    
           protected function getRestrictionQuery() 
        
           { 
        
               return $this->query; 
        
           }

framework/src/Illuminate/Database/Eloquent/Relations/Concerns/CanBeOneOfMany.php

Lines 221 to 226 in e16066d

    
           protected function getRestrictionQuery() 
        
           { 
        
               return $this->isOneOfMany() 
        
                   ? $this->oneOfManySubQuery 
        
                   : $this->query; 
        
           }

ping #37362

taylorotwell · May 21, 2021

So, won't addConstraints be called twice for these queries?

cbl · May 21, 2021

@taylorotwell It is called by the constructor, however at that point it doesnt know that the relationship is one-of-many so it must be called again when ofMany is called, so the constraints will be added to the sub query as well.

taylorotwell · May 21, 2021

So are the constraints added to both the sub query and the "main" query?

cbl · May 21, 2021

@taylorotwell yes, but that does not effect the performance or the results at all, so not necessary to remove the constraint from the parent builder.

GrahamCampbell · May 21, 2021

-     * @param  \Closure|string|null  $column
-     * @param  string|null  $relation
+     * @param  string|null  $column
+     * @param  string|\Closure|null  $aggregate


This order should not have changed.

Okay, I guessed that types are ordered by probability. Just noticed the laravel docs show this exact example.

cbl added 6 commits May 20, 2021 10:53

add preserve to query builder

fb5851d

Apply fixes from StyleCI

b8c3fcd

improved one-of-many performance

80b2295

updated preserve to beforeQuery

37299e8

add constraints to one-of-many subquery

39cebee

Apply fixes from StyleCI

e16066d

driesvints reviewed May 21, 2021

View reviewed changes

Comment thread src/Illuminate/Database/Query/Builder.php Outdated

formatting

8142265

formatting

fec222f

taylorotwell merged commit d6831e9 into laravel:8.x May 21, 2021

GrahamCampbell reviewed May 21, 2021

View reviewed changes

taai mentioned this pull request Aug 31, 2022

Unnecessary query parameter (_id is not null) in Eloquent relationships #43948

Closed

bert-w mentioned this pull request May 1, 2023

[10.x] Add HasOneOrMany::shouldAddForeignKeyCheck() #46916

Closed

SachinAgarwal1337 mentioned this pull request Mar 22, 2026

Eager load constraints on latestOfMany/ofMany not applied to inner subquery #59318

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

[8.x] Improve one-of-many performance - #37451

[8.x] Improve one-of-many performance#37451
taylorotwell merged 8 commits into
laravel:8.xlaravel/framework:8.xfrom
cbl:one-of-many_performanceCopy head branch name to clipboard

cbl commented May 21, 2021 •

edited

Loading

Uh oh!

Uh oh!

taylorotwell commented May 21, 2021

Uh oh!

cbl commented May 21, 2021 •

edited

Loading

Uh oh!

taylorotwell commented May 21, 2021

Uh oh!

cbl commented May 21, 2021 •

edited

Loading

Uh oh!

GrahamCampbell May 21, 2021

Uh oh!

cbl May 22, 2021

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

	protected function getRestrictionQuery()
	{
	return $this->query;
	}

	protected function getRestrictionQuery()
	{
	return $this->isOneOfMany()
	? $this->oneOfManySubQuery
	: $this->query;
	}

Search code, repositories, users, issues, pull requests...

Uh oh!

Conversation

cbl commented May 21, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Background

Solution

How It Works

Uh oh!

Uh oh!

taylorotwell commented May 21, 2021

Uh oh!

cbl commented May 21, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

taylorotwell commented May 21, 2021

Uh oh!

cbl commented May 21, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

GrahamCampbell May 21, 2021

Choose a reason for hiding this comment

Uh oh!

cbl May 22, 2021

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

cbl commented May 21, 2021 •

edited

Loading

cbl commented May 21, 2021 •

edited

Loading

cbl commented May 21, 2021 •

edited

Loading