Proxima Fermata

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入れました。