Eloquentはちゃんと遅いよという話

結論

どうしてもCollectionやEloquentModelとして扱いたい場合以外はDBファサードでクエリちゃんと書いた方がパフォーマンスが良い。

検証

環境

• mysql8.0
• php8.1
• laravel9

個人的に作成しているアプリで、ちょうどいい例があったので検証
市区町村ごとの人口統計をベースにゴニョゴニョしたかったので、適当にDBを作成
統計データは2020しかないので各市町村ごとに1件のみ

リレーションイメージ

 - 都道府県テーブル(47件）
  - 市区町村テーブル(1800件くらい)
    - 人口総計テーブル(1件）
    - 男性人口テーブル(1件）
    - 女性人口テーブル(1件）
    - 15歳以下人口テーブル(1件）
    - 15-64歳人口テーブル(1件）
    - 65歳以上人口テーブル(1件）
    - 世帯数テーブル(1件）
    - 核家族世帯数テーブル(1件）
    - 夫婦と子供のみ世帯数テーブル(1件）
    - シングルファザー世帯数テーブル(1件）
    - シングルマザー世帯数テーブル(1件）

市区町村のEloquentモデルにwithプロパティを設定して、市区町村以下の統計データを全部持ってこさせる。
簡易的にwebルート内で処理を書いてしまう。

web.php

Route::get('/dashboard', function () {
    $start = microtime(true);
    \Illuminate\Support\Facades\DB::enableQueryLog();
    $areas = \App\Models\Prefecture::with('areas')->get();
    $query = \Illuminate\Support\Facades\DB::getQueryLog();
    $end = microtime(true);
    $diff = $end - $start;
    return Inertia::render('Dashboard', [
        'areas' => $areas,
        'seconds' => $diff,
        'start' => $start,
        'end' => $end,
        'query' => $query
    ]);
})->middleware(['auth', 'verified'])->name('dashboard');

結果
発行されたクエリは多いものの、クエリ自体は遅くない。
市区町村が大体1800件くらいあるのでwhere inのIDは省略。

query:Array[13]
0:Reactive
query:"select * from `prefectures`"
bindings:Array[0]
time:2.5
1:Reactive
query:"select * from `areas` where `areas`.`prefecture_id` in (1,...,47)"
bindings:Array[0]
time:13.22
2:Reactive
query:"select * from `area_populations` where `area_populations`.`area_id` in (1, 2,... (10580 total length)"
bindings:Array[0]
time:29.66
3:Reactive
query:"select * from `area_population_males` where `area_population_males`.`area_id` in (1, ... (10590 total length)"
bindings:Array[0]
time:9.29
4:Reactive
query:"select * from `area_population_females` where `area_population_females`.`area_id` in (1, ... (10594 total length)"
bindings:Array[0]
time:9.39
5:Reactive
query:"select * from `area_population_age15to64s` where `area_population_age15to64s`.`area_id` in (1, ... (10600 total length)"
bindings:Array[0]
time:10.68
6:Reactive
query:"select * from `area_population_age_over65s` where `area_population_age_over65s`.`area_id` in (1, ... (10602 total length)"
bindings:Array[0]
time:11.68
7:Reactive
query:"select * from `area_population_age_under15s` where `area_population_age_under15s`.`area_id` in (1,... (10604 total length)"
bindings:Array[0]
time:9.62
8:Reactive
query:"select * from `area_families` where `area_families`.`area_id` in (1,... (10574 total length)"
bindings:Array[0]
time:10.6
9:Reactive
query:"select * from `area_family_nuclears` where `area_family_nuclears`.`area_id` in (1,... (10588 total length)"
bindings:Array[0]
time:8.43
10:Reactive
query:"select * from `area_family_partner_and_children` where `area_family_partner_and_children`.`area_id` in (1,1833, 1834,... (10612 total length)"
bindings:Array[0]
time:8.87
11:Reactive
query:"select * from `area_family_single_fathers` where `area_family_single_fathers`.`area_id` in (1,... (10600 total length)"
bindings:Array[0]
time:8.89
12:Reactive
query:"select * from `area_family_single_mothers` where `area_family_single_mothers`.`area_id` in (1,... (10600 total length)"
bindings:Array[0]
time:9.05

DBからデータ取得してコレクションとして返されるまでの時間が大体1.5秒くらい

seconds:1.4108381271362305

同じことをクエリビルダーで雑にやる

web.php

Route::get('/dashboard', function () {
    $start = microtime(true);
    \Illuminate\Support\Facades\DB::enableQueryLog();
        $areas = \Illuminate\Support\Facades\DB::table('prefectures')
        ->join('areas', 'prefectures.id', '=', 'areas.prefecture_id')
        ->join('area_populations',function ($join) {
            return $join->on('areas.id', '=', 'area_populations.area_id')->where('area_populations.year', '2020-01-01');
        })
        ->join('area_population_males',function ($join) {
            return $join->on('areas.id', '=', 'area_population_males.area_id')->where('area_population_males.year', '2020-01-01');
        })
        ->join('area_population_females',function ($join) {
            return $join->on('areas.id', '=', 'area_population_females.area_id')->where('area_population_females.year', '2020-01-01');
        })
        ->join('area_population_age_under15s',function ($join) {
            return $join->on('areas.id', '=', 'area_population_age_under15s.area_id')->where('area_population_age_under15s.year', '2020-01-01');
        })
        ->join('area_population_age15to64s',function ($join) {
            return $join->on('areas.id', '=', 'area_population_age15to64s.area_id')->where('area_population_age15to64s.year', '2020-01-01');
        })
        ->join('area_population_age_over65s',function ($join) {
            return $join->on('areas.id', '=', 'area_population_age_over65s.area_id')->where('area_population_age_over65s.year', '2020-01-01');
        })
        ->join('area_families',function ($join) {
            return $join->on('areas.id', '=', 'area_families.area_id')->where('area_families.year', '2020-01-01');
        })
        ->join('area_family_nuclears',function ($join) {
            return $join->on('areas.id', '=', 'area_family_nuclears.area_id')->where('area_family_nuclears.year', '2020-01-01');
        })
        ->join('area_family_partner_and_children',function ($join) {
            return $join->on('areas.id', '=', 'area_family_partner_and_children.area_id')->where('area_family_partner_and_children.year', '2020-01-01');
        })
        ->join('area_family_single_mothers',function ($join) {
            return $join->on('areas.id', '=', 'area_family_single_mothers.area_id')->where('area_family_single_mothers.year', '2020-01-01');
        })
        ->join('area_family_single_fathers',function ($join) {
            return $join->on('areas.id', '=', 'area_family_single_fathers.area_id')->where('area_family_single_fathers.year', '2020-01-01');
        })
        ->select(
            'prefectures.name as prefecture_name',
            'areas.name as areas_name',
            'area_populations.number as area_population',
            'area_population_males.number as area_population_male',
            'area_population_females.number as area_population_female',
            'area_population_age_under15s.number as area_population_age_under15',
            'area_population_age15to64s.number as area_population_age15to64',
            'area_population_age_over65s.number as area_population_age_over65',
            'area_families.number as area_family',
            'area_family_nuclears.number as area_family_nuclear',
            'area_family_partner_and_children.number as area_family_partner_and_children',
            'area_family_single_mothers.number as area_family_single_mother',
            'area_family_single_fathers.number as area_family_single_fathers'
        )
        ->get();
    $query = \Illuminate\Support\Facades\DB::getQueryLog();
    $end = microtime(true);
    $diff = $end - $start;
    return Inertia::render('Dashboard', [
        'areas' => $areas,
        'seconds' => $diff,
        'start' => $start,
        'end' => $end,
        'query' => $query
    ]);
})->middleware(['auth', 'verified'])->name('dashboard');

クエリログは1件のみ。

querylog

0:Reactive
query:"select `prefectures`.`name` as `prefecture_name`, `areas`.`name` as `areas_name`, `area_populations`.`number` as `area_population`, `area_population_males`.`number` as `area_population_male`, `area_population_females`.`number` as `area_population_female`, `area_population_age_under15s`.`number` as `area_population_age_under15`, `area_population_age15to64s`.`number` as `area_population_age15to64`, `area_population_age_over65s`.`number` as `area_population_age_over65`, `area_families`.`number` as `area_family`, `area_family_nuclears`.`number` as `area_family_nuclear`, `area_family_partner_and_children`.`number` as `area_family_partner_and_children`, `area_family_single_mothers`.`number` as `area_family_single_mother`, `area_family_single_fathers`.`number` as `area_family_single_fathers` from `prefectures` inner join `areas` on `prefectures`.`id` = `areas`.`prefecture_id` inner join `area_populations` on `areas`.`id` = `area_populations`.`area_id` and `area_populations`.`year` = ? inner join `area_population_males` on `areas`.`id` = `area_population_males`.`area_id` and `area_population_males`.`year` = ? inner join `area_population_females` on `areas`.`id` = `area_population_females`.`area_id` and `area_population_females`.`year` = ? inner join `area_population_age_under15s` on `areas`.`id` = `area_population_age_under15s`.`area_id` and `area_population_age_under15s`.`year` = ? inner join `area_population_age15to64s` on `areas`.`id` = `area_population_age15to64s`.`area_id` and `area_population_age15to64s`.`year` = ? inner join `area_population_age_over65s` on `areas`.`id` = `area_population_age_over65s`.`area_id` and `area_population_age_over65s`.`year` = ? inner join `area_families` on `areas`.`id` = `area_families`.`area_id` and `area_families`.`year` = ? inner join `area_family_nuclears` on `areas`.`id` = `area_family_nuclears`.`area_id` and `area_family_nuclears`.`year` = ? inner join `area_family_partner_and_children` on `areas`.`id` = `area_family_partner_and_children`.`area_id` and `area_family_partner_and_children`.`year` = ? inner join `area_family_single_mothers` on `areas`.`id` = `area_family_single_mothers`.`area_id` and `area_family_single_mothers`.`year` = ? inner join `area_family_single_fathers` on `areas`.`id` = `area_family_single_fathers`.`area_id` and `area_family_single_fathers`.`year` = ?"
bindings:Array[11]
time:63.78

所要時間はほぼ同義なのだけれど0.06秒とEloquentと比べかなり早い。

seconds:0.06791186332702637

数件〜数十件だとあまり体感しにくいけど、数百数千の段階でもう体感スピード全然違うので、どうしてもeloquentの形式でデータを扱いたい理由がない限りはクエリビルダー経由でもrawで直書きでもいいのでSQL書いた方が良い。