MySQLのJOINが遅いというのはよく言われるが、複数回SQLを発行するのとJOINするのがどちらがましなのか疑問だったので、実際どのくらい遅いのか試してみた。
Rails3.2のプロジェクトを作成し、適当なモデルを作ってconsoleでSQLの実行時間を見る。
サンプルコードはgithubに置いた。
モデルはUser -< Item -< Extraで、Userは100、Itemは10ずつで1000、Extraは10ずつで合計10000のレコードを作成することにする。
seed_fuのフィクスチャを使い、rake db:seed_fuで一気にレコードが作成されるようにした。
この状態（commit:830f6dae26189d4ea83b8753471a166712d50568）で全てのエントリを取得させてみた。
User全件(100件)
> User.all.count # SELECT `users`.* FROM `users` => 1.0ms
Item全件(100*10件)
1.JOINした場合
> User.joins(:items).all.count # SELECT `users`.* FROM `users` INNER JOIN `items` ON `items`.`user_id` = `users`.`id` => 3.2ms
2.JOINせず複数回SQLした場合
> Item.where(:user_id => User.where(:id => {:not => nil}).pluck(:id)).all.count
# SELECT id FROM `users` WHERE (`users`.`id` IS NOT NULL)
# SELECT `items`.* FROM `items` WHERE `items`.`user_id` IN (1..100)
=> 0.6ms + 4.5ms = 5.1ms
Extra全件(100*10*10件)
1.JOINした場合
> User.joins(:items => :extras).all.count # SELECT `users`.* FROM `users` INNER JOIN `items` ON `items`.`user_id` = `users`.`id` INNER JOIN `extras` ON `extras`.`item_id` = `items`.`id` => 35.4ms
2.JOINせず複数回SQLした場合
> Extra.where(:item_id => (Item.where(:user_id => User.where(:id => {:not => nil}).pluck(:id)).pluck(:id))).all.count
# SELECT id FROM `users` WHERE (`users`.`id` IS NOT NULL)
# SELECT id FROM `items` WHERE `items`.`user_id` IN (1..100)
# SELECT `extras`.* FROM `extras` WHERE `extras`.`item_id` IN (1..1000)
=> 0.6ms + 1.8ms + 30.9ms = 33.3ms
このケースだとあまり差が出ないので、Extraの件数を10倍にしてみた。（commit:96ee43e9319a363666033ac725950283bb80fab8）
Extra全件(100*10*100件)
1.JOINした場合
> User.joins(:items => :extras).all.count # SELECT `users`.* FROM `users` INNER JOIN `items` ON `items`.`user_id` = `users`.`id` INNER JOIN `extras` ON `extras`.`item_id` = `items`.`id` => 358.9ms
2.JOINせず複数回SQLした場合
> Extra.where(:item_id => (Item.where(:user_id => User.where(:id => {:not => nil}).pluck(:id)).pluck(:id))).all.count
# SELECT id FROM `users` WHERE (`users`.`id` IS NOT NULL)
# SELECT id FROM `items` WHERE `items`.`user_id` IN (1..100)
# SELECT `extras`.* FROM `extras` WHERE `extras`.`item_id` IN (1..1000)
=> 0.7ms + 1.9ms + 303.0ms = 305.6ms
件数が増えるほど差が広がっている。件数が多い時はJOINしない方が良さそうではある。
まぁ、300msもかかるようだと他の高速化手段を取るべきだろうけど。
ついでにindexを追加してみた。（commit:bac53fb2569b5a681a6bacad3e8c13535e4aa141）
Item全件(100*10件)
1.JOINした場合
> User.joins(:items).all.count # SELECT `users`.* FROM `users` INNER JOIN `items` ON `items`.`user_id` = `users`.`id` => 4.0ms
2.JOINせず複数回SQLした場合
> Item.where(:user_id => User.where(:id => {:not => nil}).pluck(:id)).all.count
# SELECT id FROM `users` WHERE (`users`.`id` IS NOT NULL)
# SELECT `items`.* FROM `items` WHERE `items`.`user_id` IN (1..100)
=> 0.7ms + 4.7ms = 5.4ms
Extra全件(100*10*100件)
1.JOINした場合
> User.joins(:items => :extras).all.count # SELECT `users`.* FROM `users` INNER JOIN `items` ON `items`.`user_id` = `users`.`id` INNER JOIN `extras` ON `extras`.`item_id` = `items`.`id` => 212.1ms
2.JOINせず複数回SQLした場合
> Extra.where(:item_id => (Item.where(:user_id => User.where(:id => {:not => nil}).pluck(:id)).pluck(:id))).all.count
# SELECT id FROM `users` WHERE (`users`.`id` IS NOT NULL)
# SELECT id FROM `items` WHERE `items`.`user_id` IN (1..100)
# SELECT `extras`.* FROM `extras` WHERE `extras`.`item_id` IN (1..1000)
=> 0.6ms + 3.3ms + 556.9ms = 560.8ms
む、逆転した。
以上はローカル環境で適当にやっただけなので、そのうちAWSのインスタンスでも立ててちゃんと調べよう。
追記：分かりやすいようにSQL入れました。