创建论坛灯泡（未读）系统的最有效方法是什么？

Alright, another interesting problem over at Route 50.

好吧,在50号公路上另一个有趣的问题。

We wanted to implement a true forum lightbulb system where posts that are unread by a user (after the user's account is created) show as unread until that status is cleared or until the user reads them.

我们希望实现一个真正的论坛灯泡系统,其中用户未读的帖子(在创建用户帐户之后)显示为未读,直到该状态被清除或用户读取它们为止。

We figured the best and easiest way to do this would be to implement a table of unread messages.

我们认为最好和最简单的方法是实现一个未读消息表。

The Columns are: user_id, board_id, thread_id, post_id, timestamp, and hidden

列是:user_id,board_id,thread_id,post_id,timestamp和hidden

This is working very well and very quickly for seeing which boards/threads/posts are unread (and linking to them) per user, however it is INCREDIBLY slow for a user to post to the forum even though only a single SQL query is being run:

这对于查看每个用户未读(并链接到它们)的板/线程/帖子非常有效且非常快,但是即使只运行一个SQL查询,用户发布到论坛的速度也很慢:

INSERT IGNORE INTO `forums_lightbulb` SELECT `id`,'x','x','x',UNIX_TIMESTAMP(),0 FROM `users`

I'm sure this is the result of having 3065 user accounts. How can I speed up this process? I'd prefer to keep the system as Real-Time as possible.

我确定这是拥有3065个用户帐户的结果。我怎样才能加快这个过程?我宁愿尽可能保持系统的实时性。

Important Note: Please limit your answers to a shared hosting environment with no additional budget. We are limited to PHP and MySQL 5.1.53-log

重要说明:请将您的答案限制在共享托管环境中,无需额外预算。我们仅限于PHP和MySQL 5.1.53-log

4 个解决方案

#1

What PHPBB does is a very quick way to do it. It keeps a table that marks for each thread and each forum when the last time was a user opened it. And uses that to determine if there are unread messages. It allows a Users*Topics + Users*Forums storage usage scheme while allowing a check with pretty simple and fast queries.

PHPBB做的是一种非常快速的方法。它保留了一个表,用于标记每个线程和每个论坛的最后一次是用户打开它的时间。并使用它来确定是否有未读消息。它允许用户*主题+用户*论坛存储使用方案,同时允许使用非常简单和快速的查询进行检查。

You can see how it works from the database structure.

您可以从数据库结构中查看它的工作原理。

# Table: 'phpbb_forums_track'
CREATE TABLE phpbb_forums_track (
    user_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    forum_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    mark_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
    PRIMARY KEY (user_id, forum_id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

# Table: 'phpbb_topics_track'
CREATE TABLE phpbb_topics_track (
    user_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    forum_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    mark_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
    PRIMARY KEY (user_id, topic_id),
    KEY topic_id (topic_id),
    KEY forum_id (forum_id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

#2

Sorry to say, but the solution proposed in the question is an unscalable design.

很抱歉,但问题中提出的解决方案是不可扩展的设计。

I was looking into this problem over here, which had a decent discussion on it (before I showed up). Take a look there.

我在这里讨论这个问题,对它进行了不错的讨论(在我出现之前)。看看那里。

In your case, storing U*M records to track "unread" posts, where U is the number of users and M is the number of messages, would get out of control very, very quickly. This is because its best-case efficiency requires all users to read every message (and most users don't care about everything, as most everything on a forum is noise). Average case, maybe 20% of users have read 100% of posts, but 80% have read near 0% of posts and never will read the rest. This means that you're being forced to store 0.8*U*M, with U and M only ever increasing, geometrically. No amount of indexing will fix this.

在您的情况下,存储U * M记录以跟踪“未读”帖子,其中U是用户数,M是消息数,将非常非常快速地失控。这是因为它的最佳效率需要所有用户阅读每条消息(并且大多数用户并不关心所有内容,因为论坛上的大多数内容都是噪音)。平均情况下,可能有20%的用户已经阅读了100%的帖子,但80%的用户已经阅读了近0%的帖子,而且从未阅读其余的帖子。这意味着您*存储0.8 * U * M,而U和M仅在几何上增加。没有多少索引会解决这个问题。

The previous @will-hartung answer has the more efficient approach.

之前的@ will-hartung答案具有更高效的方法。

I see that this is pretty old, and I hope you found a better solution in the meantime.

我发现这已经很老了,我希望你在此期间能找到更好的解决方案。

#3

Here is the most efficient way:

这是最有效的方式:

have a table called read_threads which stores the thread_id, and user_id

有一个名为read_threads的表,它存储了thread_id和user_id

have a column in the users table called mark_read_date which stores the date the user clicked on the mark all threads read link in your forum

在users表中有一个名为mark_read_date的列,它存储用户在标记中点击所有线程读取链接的日期

in order to determine if a thread is read, your query will check if it is in the read_threads table, or if its last_post_date (the date the last post was made to it) is older than the users mark_read_date

为了确定是否读取了一个线程,您的查询将检查它是否在read_threads表中,或者它的last_post_date(最后一个帖子的日期)是否早于用户mark_read_date

It is important that you also remove all rows from the read_threads table when a user clicks the mark all threads read link in your forum.

当用户单击论坛中所有线程读取标记链接时,还必须从read_threads表中删除所有行。

#4

On read:

insert into read_articles(user_id, article_id);

On display:

SELECT a.*, r.user_id FROM articles a 
LEFT OUTER JOIN read_articles r ON (a.article_id = r.article_id and r.user_id = $user_id)
WHERE (article_filter, like forum or thread id, or whatever)

On your result set, if user_id is not null, then they've read the article. Otherwise, they haven't.

在结果集上,如果user_id不为null,那么他们已经阅读了文章。否则,他们没有。

Index as appropriate. Server warm with biscuits and jam.

指数酌情。服务器温暖与饼干和果酱。

#1