symfony1.0でMySQLからランダムにレコードを取得する方法、パフォーマンスについて

方法1

簡単なのは以下の方法です。
この方法だと、テーブルのカラム数やレコード数が多い場合に著しくパフォーマンスが低下します。

コード

1件取得する場合

$c = new Criteria();
$c->addDescendingOrderByColumn('RAND()');
$c->setLimit(1);
$random_record = MyTablePeer::doSelectOne($c);

5件取得する場合

$c = new Criteria();
$c->addDescendingOrderByColumn('RAND()');
$c->setLimit(5);
$random_records = MyTablePeer::doSelect($c);

生成されるSQL文

1件取得する場合

SELECT my_table.COLUMN1, my_table.COLUMN2, ...
  FROM my_table ORDER BY RAND() DESC LIMIT 1

5件取得する場合

SELECT my_table.COLUMN1, my_table.COLUMN2, ...
  FROM my_table ORDER BY RAND() DESC LIMIT 5

解説

MySQLは内部的に以下のような手順でこのクエリを処理します。

まず"ORDER BY RAND() DESC"の部分。
それぞれのレコードについてRAND()を計算し、その結果のカラムを各レコードに追加します。そして、そのカラムをキーとしてソート(この場合はDESCなので降順)します。
そして、"LIMIT 5"により上位5件のデータのみを残し、"SELECT my_table.COLUMN1, my_table.COLUMN2,..."によって指定されたレコードセットをクライアントに送信します。

つまり、処理の過程において、テーブルの取得したいカラム全て＋RAND()について全レコードの結果をソートした以下のような中間テーブルを生成しているのです。

+------+----------+    +-------------------+
|  id  | title    |... |   RAND()          |
+------+----------+    +-------------------+
| 1035 | Symfony  |    |  0.87571515969533 |
|   43 | MySQL    |    |  0.40903770216651 |
|  342 | Propel   |    | 0.151462722710765 |
|   10 | Criteria |    | 0.130168165201478 |
|    1 | PHP      |... | 0.056491127290289 |
|  .   |    .     |    |          .        |
|  .   |    .     |    |          .        |
|  .   |    .     |    |          .        |
|  .   |    .     |    |          .        |
+------+----------+    +-------------------+

このため、カラムやレコード数が多い場合には非常に多くの計算量を必要とします。

方法2

以下のように記述すると、レコード数が増えてもパフォーマンスをあまり低下させる事なく取得できます。
コード量が多いですが、モデル側(MyTablePeer.php)に記述してしまえば問題ないでしょう。

コード

1件取得する場合

$c = new Criteria();
$c->addSelectColumn(MyTablePeer::ID);//インデックスの作成されたカラム(可能な限り主キー)を指定
$c->addDescendingOrderByColumn('RAND()');
$c->setLimit(1);
$rs = MyTablePeer::doSelectRS($c);

$rs->next();
$random_id = $rs->getInt(1);

$random_record = MyTablePeer::retrieveByPk($random_id);

5件取得する場合

$c = new Criteria();
$c->addSelectColumn(MyTablePeer::ID);//インデックスの作成されたカラム(可能な限り主キー)を指定
$c->addDescendingOrderByColumn('RAND()');
$c->setLimit(5);
$rs = MyTablePeer::doSelectRS($c);

$random_ids = array();
while($rs->next()){
  $random_ids[] = $rs->getInt(1);
}
$random_records = MyTablePeer::retrieveByPks($random_ids);

生成されるSQL文

1件取得する場合

SELECT my_table.ID FROM my_table ORDER BY RAND() DESC LIMIT 1

SELECT my_table.COLUMN1, my_table.COLUMN2, ...
  FROM my_table WHERE my_table.ID = <ランダムなID>

5件取得する場合

SELECT my_table.ID FROM my_table ORDER BY RAND() DESC LIMIT 5

SELECT my_table.COLUMN1, my_table.COLUMN2, ...
  FROM my_table WHERE my_table.ID IN (<ランダムなIDのリスト>)

解説

方法1で説明したとおり、"ORDER BY RAND()"を使ったクエリの処理は、テーブルの取得したいカラム全て＋RAND()について全レコードのソート結果を中間テーブルとして生成します。
それならばこのクエリで取得するカラムは最小限（主キーのみ）にとどめた方が中間テーブルのサイズは小さくなり、当然速くなります。

残念なことに、SymfonyのORMであるPropelは、全てのカラムがそろっていないとレコードセットをモデルオブジェクトに変換してくれません。
なので、doSelectRS()メソッドを用いて、1つめのSQLクエリの結果をリソースの形で得るようにしています。
そして2つ目のクエリでお目当てのレコードを取得します。主キーアクセスは非常に高速なのですぐに結果が返ります。

方法3

方法2と比較してパフォーマンスの低下はさらに緩やかになりますが、取得するデータのテーブル上での位置（オフセット）によって応答時間が変化します。

複数件取得する場合、テーブル上で連続するレコードを取り出すのであれば時間は変わりませんが、それぞれのレコードを完全にランダムに選び出そうとすると、取得件数にほぼ比例した時間がかかってしまいます。

コード

1件取得する場合

$con = Propel::getConnection(MyTablePeer::DATABASE_NAME);
$sql = 'SELECT FLOOR(RAND()*(SELECT count(*) FROM my_table))';
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
$rs->next();
$random_offset = $rs->getInt(1);

$c = new Criteria();
$c->setOffset($random_offset);
$this->random_record = MyTablePeer::doSelectOne($c);

5件取得する場合

各レコードをランダム取得する場合

$con = Propel::getConnection(MyTablePeer::DATABASE_NAME);
$sql = 'SELECT FLOOR(RAND()*(SELECT count(*) FROM my_table))';
$stmt = $con->createStatement();
$random_offsets = array();
while(count($random_offsets)< 5){
  $rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
  $rs->next();
  $random_offset = $rs->getInt(1);
  if(!in_array($random_offset, $random_offsets)) $random_offsets[] = $random_offset;
}

$random_records = array();
foreach($random_offsets as $random_offset){
  $c = new Criteria();
  $c->setOffset($random_offset);
  $random_records[] = MyTablePeer::doSelectOne($c);
}
$this->random_records = $random_records;

連続した5件でよい場合(SQL文の'-4'に注意!!)

$con = Propel::getConnection(MyTablePeer::DATABASE_NAME);
$sql = 'SELECT FLOOR(RAND()*((SELECT COUNT(*) FROM my_table)-4))';
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
$rs->next();
$random_offset = $rs->getInt(1);

$c = new Criteria();
$c->setOffset($random_offset);
$c->setLimit(5);
$this->random_records = MyTablePeer::doSelect($c);

生成されるSQL文

1件取得する場合

SELECT FLOOR(RAND()*(SELECT count(*) FROM my_table))

SELECT my_table.COLUMN1, my_table.COLUMN2, ...
  FROM my_table LIMIT <ランダムなオフセット数値>, 1

5件取得する場合

各レコードをランダム取得する場合

※5回以上繰り返し
SELECT FLOOR(RAND()*(SELECT count(*) FROM my_table))

※5回繰り返し
SELECT my_table.COLUMN1, my_table.COLUMN2, ...
  FROM my_table LIMIT <ランダムなオフセット数値>, 1

連続した5件でよい場合

SELECT FLOOR(RAND()*(SELECT count(*) FROM my_table))

SELECT my_table.COLUMN1, my_table.COLUMN2, ...
  FROM my_table LIMIT <ランダムなオフセット数値>, 5

解説

テーブルのレコード数は瞬時に求める事ができます。
0≦n＜レコード数なる整数nをランダムに生成して、それをオフセットとしてデータを1件取得すれば、ランダムなデータが高速に取得できます。

方法4

この方法が最も高速で、さらにレコード数が増加してもパフォーマンスはほぼ全く低下しませんが、この方法が使えるのは以下の条件を満たす場合のみです。

テーブルの主キー(またはインデックスを作成した候補キー)が整数値であり、すべて連続している

主キーが文字列の場合や、欠番がある場合にはこの方法は使えません（とはいっても、ある程度"密"であれば、工夫すれば何とかなります）。

コード

1件の場合のみ示します。複数件の場合も簡単です。

$con = Propel::getConnection(MyTablePeer::DATABASE_NAME);
$sql = "SELECT FLOOR((SELECT id FROM my_table ORDER BY id ASC LIMIT 1)+RAND()*((SELECT id FROM my_table ORDER BY id DESC LIMIT 1)))";
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
$rs->next();
$random_id = $rs->getInt(1);

$this->random_record = MyTablePeer::retrieveByPk($pickup_id);

生成されるSQL文

SELECT FLOOR((SELECT id FROM my_table ORDER BY id ASC LIMIT 1)+RAND()*((SELECT id FROM my_table ORDER BY id DESC LIMIT 1)))

SELECT my_table.COLUMN1, my_table.COLUMN2, ...
  FROM my_table WHERE my_table.id=<ランダムなID>

解説

インデックスが作成されたカラムの最大値、最小値は、たとえレコード数が莫大であってもほぼ瞬時に求まります。
それならば、最小値≦n≦最大値なる整数nをランダムに生成して、id=nなるレコードをSELECTすれば、あっという間にランダムなレコードが一件取得できてしまいます。それがこの方法です。
しかし、気を付けなければならないのは、主キーが連続な整数値とは限らない点です。
文字列に主キーが設定してあるかもしれませんし、レコードが大量に削除されてIDが欠番だらけになっているかもしれません。
そのような穴だらけのテーブルに対してID決め撃ちしても、うまくヒットするとは限りません。
だから、この方法を利用する場合にはIDが連続な整数値であることが保証される必要があります。

コードについては、サブクエリが確かCriteriaでは扱えないはずなので、Criteriaでの記述はあきらめ、MySQL文を直に書いています。