Edited at

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