swiftone has asked for the wisdom of the Perl Monks concerning the following question:

So I'm reinventing the wheel, but this needs to integrate with the rest of our site so Slash or Everything wouldn't easily work. Besides, I always think I can do better :)

Anyway, I'm writing up a bulletin board system, stealing ideas from any other system I can see. To improve performance in retrieving nested threads, I have a mapping field for each message. Like so:
Mapping IDNodeID
10241
20482</td
If node 3 is created in reply to Node 2, it would be inserted with Mapping ID 1536. (The real table has larger differences, to allow more levels of nesting) (Also, the parent-child relationship is recorded, but is irrelevant to this question)

So after a certain amount of nesting (I'm still working through the math), there will not be room for a new MapID, and ALL MapIDs must be recalcuated (I'd actually want to do this a few levels BEFORE required, to allow for detection time). I could either run this in a cron job, or I can have it triggered by the posting script.

My question (finally!) is a procedural one. Should I have a monitoring program check periodically, or should I have the user posting a message trip the program? Should I have the posting script do the work of recalculating the mapping IDs, or should it pass the task to an external program?

  • Comment on Perl handling of a MYSQL bulletin board

Replies are listed 'Best First'.
Re: Perl handling of a MYSQL bulletin board
by ncw (Friar) on Aug 31, 2000 at 19:45 UTC
    What makes you think that ordering the mapping IDs like that would improve performance? Joining tables on IDs is what SQL databases are all about and they do it quicker than anything else.

    You could have a thread table and put a threadid in each node. You'll be able to then fetch all items in a thread with a simple very fast join. The thread table could also have a threadid field (for a self join) for organising the nested threads

    Or am I missing something in your requirement?

[reply]
      In fact, DBIx::Tree supports the extraction of a tree of data exactly for this kind of thing. So most of the hard work is already hidden in a module (yes! leverage!). {grin}

      -- Randal L. Schwartz, Perl hacker

[reply]
        In fact, DBIx::Tree supports the extraction of a tree of data exactly for this kind of thing. So most of the hard work is already hidden in a module (yes! leverage!). {grin}

        Huh. Wheels ARE better round. Who knew?

[reply]
        Actually, upon closer inspection, I am displeased by DBIx::Tree. It works by getting all the rows in the table, then proceeding though a linear sort of the rows to put them in nested format. I'm sure I can improve upon that performance.
[reply]
      It is easy to do the join... it is hard to sort the data without a good subselect.. and due to the lacking capabilities of MySQL in that area... The join thing "fails"....(or that I'm just plain stoopid)

      JanneVee

[reply]
      I don't understand how this will allow (near)infinite-depth nesting. Am I missing something?
[reply]

Back to Seekers of Perl Wisdom