SQLiteで最も新しく追加されたレコードを高速に取得

SQLiteで管理しているデータベースファイル、特定のテーブルの一番最後に追加（更新ではない）されたレコードを取得する。 単純ですがいくつかの手法があり、どれが高速なのか実際に試してみました。

まずテストに使うDBを作成するスクリプトを作る。 コマンドラインからPHPを動かします、もちろんブラウザからでも動きます。

<?php
define ('MAX_LOOP', 400000); // クエリを実行する回数

/**
*        numbers
* |a INTEGER PRIMARY KEY|
*/
$sql = "CREATE TABLE numbers (a INTEGER PRIMARY KEY);";
LoopQuery($sql, 1);

$sql = "INSERT INTO numbers VALUES (NULL);";
LoopQuery($sql);

// End;

/**
* SQL文 を MAX_LOOP の分だけ実行しまくる
*/
function LoopQuery($sql, $max_loop = MAX_LOOP)
{
$dbh = new PDO("sqlite:./root.db");
$sth = $dbh->prepare($sql);

echo "----------------------------------------\n";
echo $sth->queryString."\n";
$s = microtime(true);
$dbh->BeginTransaction();
for($i = 0; $i < $max_loop; $i++) {
 $sth->execute();
}
$dbh->commit();

echo "time({$max_loop}): " . (microtime(true) - $s) . " sec\n";
print_r($sth->fetch(PDO::FETCH_ASSOC));
}

上記のスクリプトを保存して実行します、例えば makedb.php と保存した場合

 $ php makedb.php
----------------------------------------
CREATE TABLE numbers (a INTEGER PRIMARY KEY);
time(1): 0.02186918258667 sec
----------------------------------------
INSERT INTO numbers VALUES (NULL);
time(400000): 7.7450699806213 sec

と実行します。

試すクエリがこの三種類。

SELECT * FROM numbers WHERE rowid = (SELECT max(rowid) FROM numbers);
SELECT max(rowid),* FROM numbers;
SELECT * FROM numbers ORDER BY rowid DESC LIMIT 1;

rowid は SQLite で使う行番号です。

一つ目は 教えてgooの回答No.2 を参考しました。
二つ目は何故か最終行を取得できるようなので、一応試してみます。
三つ目は普段使っているコードです。

以下のコードで実行してみます。

<?php
define ('MAX_LOOP', 100); // クエリを実行する回数

$sql = "SELECT * FROM numbers WHERE rowid = (SELECT max(rowid) FROM numbers);";
LoopQuery($sql);

$sql = "SELECT max(rowid),* FROM numbers;";
LoopQuery($sql);

$sql = "SELECT * FROM numbers ORDER BY rowid DESC LIMIT 1;";
LoopQuery($sql);

// End

/**
* SQL文 を MAX_LOOP の分だけ実行しまくる
*/
function LoopQuery($sql, $max_loop = MAX_LOOP)
{
$dbh = new PDO("sqlite:./root.db");
$sth = $dbh->prepare($sql);

echo "----------------------------------------\n";
echo $sth->queryString."\n";
$s = microtime(true);
$dbh->BeginTransaction();
for($i = 0; $i < $max_loop; $i++) {
 $sth->execute();
}
$dbh->commit();

echo "time({$max_loop}): " . (microtime(true) - $s) . " sec\n";
print_r($sth->fetch(PDO::FETCH_ASSOC));
}

このファイルに適当な名前をつけて保存します。 試した結果がこちら

$ php db.php
----------------------------------------
SELECT * FROM numbers WHERE rowid = (SELECT max(rowid) FROM numbers);
time(100): 0.0057640075683594 sec
Array
(
  [a] => 400000
)
----------------------------------------
SELECT max(rowid),* FROM numbers;
time(100): 32.949709177017 sec
Array
(
  [max(rowid)] => 400000
  [a] => 400000
)
----------------------------------------
SELECT * FROM numbers ORDER BY rowid DESC LIMIT 1;
time(100): 0.0015640258789062 sec
Array
(
  [a] => 400000
)

100回のループでは誤差も大きいので、かなり遅かった二つ目のクエリのみを外して実行しなおします。 ループ回数を10000回に変更。

$ php db.php
----------------------------------------
SELECT * FROM numbers WHERE rowid = (SELECT max(rowid) FROM numbers);
time(10000): 0.25697994232178 sec
Array
(
  [a] => 400000
)
----------------------------------------
SELECT * FROM numbers ORDER BY rowid DESC LIMIT 1;
time(10000): 0.15897989273071 sec
Array
(
  [a] => 400000
)

何度か試したり、前後入れ替えたりしましたが order & desc & limit の方が高速のようでした。 ソートしている分遅くなるかと思っていただけに意外でした。

追記(2010-03-10)．

他のSQL文でも取得できる方法がありました。

SELECT sum(null),* FROM numbers; /* 関数に rowid ではなく null を入れる（order byの1,000倍遅い） */
SELECT * FROM numbers group by last_insert_rowid(); /* group by で一行だけ出てくるように調整（order by の20,000倍遅い） */

追記(2010-03-10)．

挿入に26秒かかっていたものを、prepare文を使う事で8秒強まで短縮 取得(SELECT文)に至っては、およそ10倍速くなりました（max, * を除く）。

追記(2010-03-10)．

外国のコミュニティでも、サブクエリで max を使うか order by desc limit 1 を勧めているようです。 [link] http://old.nabble.com/Getting-the-last-row-td19525962.html