PHPのテクメモ

レコメンデーションの仕組みで一般的となっている「この記事を読んだ人はこんな記事も見ています」。これをSQLで実現するにはどうすればいいかを模索していて、少し先が見えてきたのでメモしておく。

まずテーブルとしてusers_pagesを作成し、カラムは2つ（user_idとpage_id）。カラムの型はそれぞれinteger。本来はusersテーブルとpagesテーブルがあるが、とりあえずここでは考慮しない。user_idとpage_idの組み合わせを主キーとし、それぞれのカラム単体でもインデックスを張っておく。こんな感じ。

CREATE TABLE `users_pages`
(
`user_id` int(11) NOT NULL,
`page_id` int(11) NOT NULL,
PRIMARY KEY (`user_id`,`page_id`),
KEY `user_id` (`user_id`),
KEY `page_id` (`page_id`)
);

訪問者が訪れるたびに、user_idを発行し、user_idがページを閲覧するたびに、当該user_idと閲覧したページの番号（page_id）の組み合わせを行として挿入していくイメージ。

「この記事を見た人は」ということなので、記事ごとにSQL文を流す方法を考える。つまりpage_id=n（nはページ番号）。一番簡単に思いつくのはこんな感じと思う。

1. 当該page_idを閲覧したuser_idを検索する
2. 検索結果のuser_idが閲覧したpage_idを検索する（ただし当該ページは除く）
3. page_idでGROUP BYして件数をカウントする
4. ORDER BYで降順にソートする

つまりサブクエリを使う方法。しかしこれはどうも速度が出ない。1万件程度のデータ（ページ数500平均5ページビューとして2,000人のデータ）で以下のSQL文を実行してみた（nはページ番号）。

SELECT * FROM users_pages up1 WHERE up1.page_id IN (SELECT up2.user_id FROM users_pages up2 WHERE up2.page_id=n);

実行時間は・・・数分たっても結果が帰ってこなかった（非実用的）。次に自己結合を使って書き直してみた。

SELECT * FROM users_pages up1 LEFT JOIN users_pages up2 ON up1.user_id=up2.user_id WHERE up2.brand_id=n;

クエリ実行時間0.01秒。こちらは実用的な速度。後者のSQLでpage_id=nを閲覧した人が、他に閲覧したpage_idを抽出することができる。

ただこれ、50万件のデータになると処理速度がクエリ実行時間が1.25秒になった。ブラウザでSQLを流してから結果を受け取るまではすごく時間がかかった。「SELECT *」ではなくて必要なカラムを指定すれば早くなるけど。おそらく数万件程度のデータなら都度実行してもよさそうだが、それ以上はバッチ処理にしておかないとダメそう。COUNTしてORDER BYするともっと遅そうだから。

SQL苦手だな・・・。