やりたいこと
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