MySQL 各グループの中から上位n個のアイテム取得が遅いので効率化したい

Question

create table `users` (
`user_id` int auto_increment primary key,
`active` tinyint
)engine=innoDB;

create table `articles` (
`article_id` int auto_increment primary key,
`user_id` int,
`created_at` datetime
)engine=innoDB;

create table `article_metadata_ref` (
`metadata_id` int,
`article_id` int,
Primary Key(`metadata_id`, `article_id`)
);

説明用に最小サンプルを作成しました。
PHP PDO からデータベースに繋いでいます。

user テーブルのactive フラグか articles テーブルの created_at の日付をみて活動していると判断できるユーザーのアイテムn個に紐付いているmetadata_id とそのcount(*) をユーザー毎に取得するのが目的です。

自分でも作成はしたのですがかなり遅いので最適化して少しでも早くしたいと思いますので解決の糸口でも頂ければ幸いです。

試した事

まずは正直にでユーザーIDのリストを取得してから

select `user_id` from users where `active` = 1;

各ユーザーのarticle_id をn個　取得してref table とjoin

select `metadata_id`, count(*) from 
   (select `article_id` 
    from articles where `user_id` = :user_id order by created_at desc limit :n
    ) as articles
   inner join article_metadata_ref as ref on articles.`article_id` = ref.`article_id`
group by `metadata_id`

結果：
+-------------+----------+
| metadata_id | count(*) |
+-------------+----------+
|           1 |        1 |
|           2 |        1 |
+-------------+----------+
2 rows in set (0.00 sec)

とこの様な形でユーザーのmetadata_idと使用頻度を取得しました。

上記のクエリで取得したmetadata_idを他のユーザーと比較したい場合はユーザーの数だけクエリを発行する必要があってユーザー数が多いと大変時間がかかります。

なので他のユーザーのmetadata_id を同時に取得しようと思って下記のクエリを考えてみました。

select `metadata_id`, count(*) from 
   (select `article_id`, `user_id`
    from articles where `user_id` IN (:uid1, :uid2, ...) order by created_at desc limit :(n*ユーザー数)
    ) as articles
   inner join article_metadata_ref as ref on articles.`article_id` = ref.`article_id`
group by `metadata_id`,articles.`user_id`

結果：
+---------+-------------+----------+
| user_id | metadata_id | count(*) |
+---------+-------------+----------+
|       1 |           1 |        1 |
|       2 |           1 |        1 |
|       1 |           2 |        1 |
|       2 |           2 |        1 |
|       1 |           3 |        1 |
|       1 |           4 |        1 |
|       1 |           5 |        1 |
+---------+-------------+----------+
7 rows in set (0.00 sec)

ただこちらのクエリだと limit n * ユーザー数ですが 特定ユーザーのarticle数が均等では無いのでデータに偏りが出る可能性がありますので使えないと思いました。

その他にrow_number() を使って取得出来ると読みましたがmysql のバージョンが5.6 なので使えないのと変数を使って実現してもユーザーの全てのarticle のレコードにrow_number を付けてからwhere でフィルタするので結果遅くなりました。

リアルタイムでmetadata_id によるユーザー間の類似度のスコアリングはスピード的に無理そうなのでcronで先に計算したテーブルを用意しようと思いましたがそれでも数万ユーザー分の計算だと大変遅いので良い方法は無いでしょうか。

直接の解決策ではなくてもアドバイスなどコメントご教示頂ければ幸いです。
またこの様なレコメンドの実装に関してもご意見頂ければと思います。調べるとhashを使ったの高速の類似度の近似値の求め方などが出てきますがそもそもデータを用意するのが遅すぎてその様な計算の高速化以前の問題で止まっています。

Comments

[m.ts10806]

MySQLのバージョンを追記してください。

[sazi]

何に対してlimitしたいのかよく分かりません。
user_id毎にn個のarticle_idですか？
それだと、件数はn以下にしかなりませんけど、それでいいのでしょうか？

[sazi]

> articles テーブルの created_at の日付をみて活動していると判断
どんな条件ですか？

Answer 1

ちょっと重さの感覚がわからないですが、内部結合で取得ではダメですかね。

select
  users.user_id,
  ref.metadata_id,
  count(*)
from article_metadata_ref ref
inner join articles art on
art.article_id = ref.article_id
inner join users on
users.user_id = art.user_id
where users.active = 1
group by ref.metadata_id, users.user_id

Answer 2

以下の2つのテーブルにインデックスを追加しては？
・articles.user_id
・article_metadata_ref.article_id

インデックスの追加前後の
MySQL SQL実行計画の疑問解決には「とりあえずEXPLAIN」しようを質問に追記しては？