Help us understand the problem. What is going on with this article?

EloquentのwhereHasメソッドは辛い

More than 1 year has passed since last update.

やりたいこと

1対NあるいはN対NをEloquentのリレーションシップで表現(hasManyとか)し、それを用いて、子テーブル側の特定カラムに条件マッチしたレコードを取得する。

環境

Lavavel 5.5
MySQL 5.7.17

whereHasを使うと何が起こるか

Eloquentではリレーションシップを定義できるので、それを用いて上記のレコード取得を実現しようとした場合、whereHasを使う必要がある。

<?php

namespace App\Models;

use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable
{
    //(中略)
    public function skills()
    {
        return $this->belongsToMany('App\Models\Skill', 'user_skills');
    }
    //(中略)
    public function scopeHasSkills($query, $options)
    {
        if (!isset($options['skills'])) {
            return;
        }

        return $query->whereHas(
            'skills',
            function ($query) use ($options) {
                $query->whereIn('skill_id', $options['skills']);
            }
        );
    }
}

skill_idに1を指定して上記のメソッドを呼び出すと、発行されるクエリは以下となる。

select distinct * from `users`
    where exists (
        select * from `skills`
        inner join `user_skills` on `skills`.`id` = `user_skills`.`skill_id`
        where `users`.`id` = `user_skills`.`user_id`
        and `skill_id` in ('1')
);

これの実行計画を調べると・・・

mysql> explain select distinct * from `users` where exists (select * from `skills` inner join `user_skills` on `skills`.`id` = `user_skills`.`skill_id` where `users`.`id` = `user_skills`.`user_id` and `skill_id` in ('1'));
+----+--------------------+-------------+------------+-------+----------------------------------------------------------+------------------------------+---------+-------+------+----------+------------------------------------+
| id | select_type        | table       | partitions | type  | possible_keys                                            | key                          | key_len | ref   | rows | filtered | Extra                              |
+----+--------------------+-------------+------------+-------+----------------------------------------------------------+------------------------------+---------+-------+------+----------+------------------------------------+
|  1 | PRIMARY            | users       | NULL       | ALL   | NULL                                                     | NULL                         | NULL    | NULL  |    1 |   100.00 | Using where                        |
|  2 | DEPENDENT SUBQUERY | skills      | NULL       | const | PRIMARY                                                  | PRIMARY                      | 4       | const |    1 |   100.00 | Using index                        |
|  2 | DEPENDENT SUBQUERY | user_skills | NULL       | ref   | user_skills_user_id_foreign,user_skills_skill_id_foreign | user_skills_skill_id_foreign | 4       | const |    1 |    50.00 | Using index condition; Using where |
+----+--------------------+-------------+------------+-------+----------------------------------------------------------+------------------------------+---------+-------+------+----------+------------------------------------+
3 rows in set, 2 warnings (0.00 sec)

お分かりいただけただろうか。なんと、MySQLにおいてパフォーマンス劣化の元凶として目の敵にされているDEPENDENT SUBQUERY がガッツリ発生するのだ。

これはMySQL5.6以上でもsemijoin 等への変換がかからないため、MySQLのバージョンアップでは解決しない。

解決策はあるのか?

リレーションシップの使用を諦め、素直にクエリビエルダでjoinするしかなさそう。その証拠に、Laravelのissueでは以下のような会話が繰り広げられていたのであった。。。
WhereHas query is too slow #18415

hisash
bm-sms
高齢社会に適した情報インフラを構築することで価値を創造し社会に貢献し続ける
https://www.bm-sms.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away