70
57

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

EloquentのwhereHasメソッドは辛い

Last updated at Posted at 2017-12-27

やりたいこと

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

70
57
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
70
57

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?