Designing a multi-threaded comment system

Question

What would be the best way to develop a streaming comment system so that it does not clog the database?

Answer 1

A modified pre-order tree traversal (or what Matt is called a "nested set") is the way to go.

If you work in Django, there is a third-party application, django-mptt , which makes the implementation of MPTT in your models one-line.

Answer 2

I assume that your question is to organize the system, so you do not need to work like:

• Select all top level comments.
• Select all comments whose parents were found in the previous step.
• Select all comments whose parents were found in the previous step.
• ... repeat until comments are found

I would suggest desiging the db table with the stream key, which would be the row of all the parents of this message. You should limit your discussion to a certain depth, but your sql statements will be direct choices and ordered using a stream key, giving you backward comments. Less taxation on your database and web server.

The stream key will look like the current message identifier connected to it by the parent type of the delimited key.

How does this sound?

Answer 3

This website lists some common methods: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

I would make a model of a "nested set", but had several roots (for example, each "theme" is a new tree). It is very fast, easy to query, but difficult to maintain ...

Answer 4

SELECT ... START WITH ... CONNECT BY

Oracle has a SELECT extension that makes it easy to retrieve tree-based data.

This query will move around the table in which the nesting relationships are stored in the parent and child columns.

select * from my_table start with parent = :TOP_ARTICLE connect by prior child = parent; 

http://www.adp-gmbh.ch/ora/sql/connect_by.html

Answer 5

What I usually do in this case is to have one thread that is responsible for putting data into the database, and all auxiliary threads report this thread, which then queues the data and writes it either sequentially or in packets (in depending on the requirements and what kind of database activity I am willing to put up with).

Answer 6

I assume that you have something similar to a comment table, with a foreign key pointing to the parent comment of each line. This makes stream comments in a tree structure with a start as the root of the tree.

So, we can rephrase the question like "How to choose a tree structure from a database?". Well, I will not assume that I know better, but my first inclination (possibly incorrect) is to use a stored procedure to walk through a tree and compile a list of returned lines. It still accepts multiple select statements to get all the children, but this is only one round trip.

An Aryeh method with an accumulated parent list is probably better :)

Answer 7

I need to include Carl Meyer's suggestions for using text in text . I am currently working on such a system, but with some further optimizations for the forums.

In forum systems that support answers, you often insert in the middle of the tree, which results in poor performance. To reduce the pain, I'm working on allowing spaces in a number line. This works as a preliminary allocation of memory in the list of arrays. The cost of switching the tree to the right is the same for 1 and 100. Therefore, with successive answers (which is more likely), I can update a smaller number of tree nodes, and they will be much faster.

Reduction is the count of descendant nodes (the number of responses below this message), comparing the current nodes on the left and right. This information can be cached in the data structure to do this quickly. Therefore, I will have to update all the ancestor nodes with the new values. Still fewer nodes will be updated, and the result will be much faster than the average insert insert time.

Several trees are stored in one table. Each tree has a unique tree identifier, one per topic. Smaller trees update much faster.

Hope this helps