MySQLでテーブルの全件をちょっとずつ持ってきて処理したいときに注意すること
「全部で100万件とか1000万件とかあるような、でっかいテーブル内のデータを全部持ってきて特定の処理を加えた後にどっかに入れる」というような処理をしたいときに注意すること。
通常はLIMITを使ってオフセット/リミットで絞ってちょっとずつやる、みたいなことをやると思いますが*1、これ実はBETWEENしてやるほうがずっと速いです。
-- こっちよりも SELECT id,name,data FROM table_name ORDER BY id ASC LIMIT 100000,1000 SELECT id,name,data FROM table_name ORDER BY id ASC LIMIT 101000,1000 SELECT id,name,data FROM table_name ORDER BY id ASC LIMIT 102000,1000 SELECT id,name,data FROM table_name ORDER BY id ASC LIMIT 103000,1000 -- こっちの方が速い！ SELECT id,name,data FROM table_name WHERE id BETWEEN 100000 AND 101000 SELECT id,name,data FROM table_name WHERE id BETWEEN 101000 AND 102000 SELECT id,name,data FROM table_name WHERE id BETWEEN 102000 AND 103000 SELECT id,name,data FROM table_name WHERE id BETWEEN 103000 AND 140000
というのはEXPLAINしてやれば明確で、LIMITのほうはLIMIT節で指定したところまでをすべて持ってきて返す直前に後ろの1000件に絞り込んでるのに対して、BETWEENのほうは予めWHERE節で持ってくる結果セットを絞り込んでいるので一瞬で結果が帰ってくるのだと思われます。LIMITのほうは後ろへいけば行くほど激遅になります。
mysql> EXPLAIN SELECT id,name,data FROM test_table ORDER BY id ASC LIMIT 900000,1000; +----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------+ | 1 | SIMPLE | test_table | index | NULL | PRIMARY | 8 | NULL | 901000 | | +----+-------------+-------------+-------+---------------+---------+---------+------+--------+-------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT id,name,data FROM test_table WHERE id BETWEEN 900000 AND 901000; +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | test_table | range | PRIMARY | PRIMARY | 8 | NULL | 1000 | Using where | +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
もちろん、上のクエリ例を見ていただければ分かると思いますが、これが出来るのは「PRIMARYキーが連番でセットされてある」*2場合に限ります（つまりLIMITでもBETWEENでも結果的に同じデータが帰ってくるときに限る）。
また、PRIMARYに数字がふられていれば、必ずしも最初から最後まで連番である必要はなく、例えば「1,2,5,7,9,10,20,21」という風にPRIMARYが歯抜けに並んでいても総件数がかなり多い場合はこちらの方が速いです。
ただし、あまりにPRIMARYが歯抜けになっている（1,5,8,100,9000,100000とか・・・）割に、総件数が少ない場合などではそもそもループする回数が多くなってしまいLIMITより時間が掛かる可能性*3があります。
その場合、（振りなおせるケースでは）PRIMARYを連番で振りなおしてしまえば処理時間を改善できます。
