I'm in the process of building web based a commenting system, for which the comments are rendered in a reddit / disqus style tree structure.

The MySQL schema uses a string of ancestor id's, and the immediate ancestor to enable me to rebuild the tree structure in memory before rendering the nested comments in HTML.

My current system for 'paging' the data, requires that I build the entire tree structure in memory, and then use the position that a comment appears in the resulting tree structure to set the position from which HTML should start and stop rendering. My concern is that this seems somewhat inefficient in that it requires that I pull all data from the database, in order to potentially just render a subset of those comments to an HTML page.

While search engine queries have returned some useful results for how to persist data in tree structures, I'm unable to find much on retrieving data from such schemas.

Is there a better / known way to retrieve this data that would satisfy the above concerns?

  • You might be better off storing your comments in a different way - have a look at the Nested Set model - en.wikipedia.org/wiki/Nested_set_model – Jez Jun 1 '15 at 14:03
  • I have looked at nested set, but if a thread can get close to 10000 comments as a maximum, then updating all records each forum post, would be too expensive. – gb2d Jun 2 '15 at 13:50
  • 10k sounds a lot, but the legacy system I'm replacing had close to this many in some threads. – gb2d Jun 2 '15 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.