Since you mention MySQL I assume that's what you're using to persist data. Storing a tree in a flat database is kind of a challenge. This page (http://www.sitepoint.com/article/hierarchical-data-database) suggests two ways: adjacency lists, and modified preorder tree traversal. MPOTT is a cute idea, but that's about it. It will make your database schema more complex, make your algorithms more complex, and require preexisting entries to be changed when new ones arrive. It's not faster than doing things in memory, and it's just asking for trouble.
Try using adjacency lists and reconstructing the tree in memory each time you serve it. This will let you get away with a single query for both reading and writing. The dev.mysql.com link you mention warns against using the adjacency list model, but that's because it's an area where MySQL itself is inadequate. It's fine when you fix things in another language.
Here's a minimal schema:
topic, id, parent, payload
ID is unique and non-zero. Parent is the ID of the parent node (0 if top-level). Topic is shared by all comments in a given topic. When you select, select by topic and then recreate your tree by going through and reestablishing "children" lists for each of your parents. You can then use traditional tree algorithms.
This method is easily modified to support caching.
I've done threaded comments with adjacency lists like this in MySQL in the past, and it worked well. Note also that it's compatible with scores moving siblings around relative to one another -- just calculate sibling position based on both score and date. Your schema might look something like this:
Since you mention MySQL I assume that's what you're using to persist data. Storing a tree in a flat database is kind of a challenge. This page (http://www.sitepoint.com/article/hierarchical-data-database) suggests two ways: adjacency lists, and modified preorder tree traversal. MPOTT is a cute idea, but that's about it. It will make your database schema more complex, make your algorithms more complex, and require preexisting entries to be changed when new ones arrive. It's not faster than doing things in memory, and it's just asking for trouble.
Try using adjacency lists and reconstructing the tree in memory each time you serve it. This will let you get away with a single query for both reading and writing. The dev.mysql.com link you mention warns against using the adjacency list model, but that's because it's an area where MySQL itself is inadequate. It's fine when you fix things in another language.
Here's a minimal schema:
topic, id, parent, payload
ID is unique and non-zero. Parent is the ID of the parent node (0 if top-level). Topic is shared by all comments in a given topic. When you select, select by topic and then recreate your tree by going through and reestablishing "children" lists for each of your parents. You can then use traditional tree algorithms.
This method is easily modified to support caching.