大学のデータベースの授業で、講師が「SQL の WHERE 句では BETWEEN 演算子を使った範囲指定よりも、比較演算子を使った範囲指定のほうが速い (または同じ速度)」と述べていました。
信じられなかったので、実験してみました。
実験環境は MacBook + PHP5.2.4 + MySQL5.0.45 です。
まず、unsigned int な id フィールドだけを持つ、test テーブルを作って、連番を 30 万件インサートします。
id フィールドにはインデックスを張っておきます。
insert.php
<?php mysql_connect('localhost', 'user', 'pass'); mysql_select_db('my_test'); mysql_query('CREATE TABLE test (id INT UNSIGNED NOT NULL)'); for ($id = 1; $id <= 300000; $id++) { mysql_query("INSERT INTO test (id) VALUES ({$id})"); } mysql_query('ALTER TABLE test ADD INDEX (id)');
% php insert.php
次に、測定です。
取得範囲を乱数でずらしながら、5 回測ってみます。
select.php
<?php mysql_connect('localhost', 'user', 'pass'); mysql_select_db('my_test'); function measure($sql) { $start = microtime(true); $ret = mysql_query($sql); do { $row = mysql_fetch_row($ret); } while ($row); return microtime(true) - $start; } for ($cnt = 1; $cnt <= 5; $cnt++) { $from = rand(1, 99); $to = rand(100000, 300000); echo "[{$cnt} 回目]\n"; echo "id: {$from} 〜 {$to}\n"; $between = measure("SELECT * FROM test WHERE id BETWEEN {$from} AND {$to}"); $comparing = measure("SELECT * FROM test WHERE id >= {$from} AND id <= {$to}"); echo "BETWEEN 演算子: {$between}\n"; echo "比較演算子: {$comparing}\n"; echo $between < $comparing ? 'BETWEEN 演算子の勝ち!!' : '比較演算子の勝ち!!'; echo "\n\n"; }
% php select.php
結果です。
[1 回目] id: 50 〜 154591 BETWEEN 演算子: 0.32147002220154 比較演算子: 0.26745891571045 比較演算子の勝ち!! [2 回目] id: 8 〜 190034 BETWEEN 演算子: 0.31467318534851 比較演算子: 0.32952904701233 BETWEEN 演算子の勝ち!! [3 回目] id: 9 〜 194446 BETWEEN 演算子: 0.32376003265381 比較演算子: 0.33915591239929 BETWEEN 演算子の勝ち!! [4 回目] id: 8 〜 216971 BETWEEN 演算子: 0.35910105705261 比較演算子: 0.37598299980164 BETWEEN 演算子の勝ち!! [5 回目] id: 93 〜 200468 BETWEEN 演算子: 0.33274006843567 比較演算子: 0.34776520729065 BETWEEN 演算子の勝ち!!
全体的に BETWEEN 演算子のほうが速いです。
たしかに、「絶対 BETWEEN のほうが速い」とは言い切れませんが、「BETWEEN が遅い」というのは誤解ではないでしょうか。