in reply to Perl handling of a MYSQL bulletin board

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?

  • Comment on Re: Perl handling of a MYSQL bulletin board

Replies are listed 'Best First'.
RE: Re: Perl handling of a MYSQL bulletin board
by merlyn (Sage) on Aug 31, 2000 at 19:54 UTC
    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]
        Yes you can improve on that performance - if you are using an object oriented database or something like DB_File. (With some sort of threading of keys - MLDBM is not the best of approaches either.)

        Otherwise the fact that hierarchical data structures do not map well onto relational databases will kill you. And firing repeated queries against the database (a very obvious guess) is really bad.

        You can beat what they do though, but not portably. And the way you do it is to create and populate a temp table on the fly with a stored procedure. Works except that all of that is mumbo jumbo that some databases (very specifically MYSQL) doesn't understand, and which tends to be non-portable from database to database.

[reply]
[reply]
RE: Re: Perl handling of a MYSQL bulletin board
by JanneVee (Friar) on Sep 01, 2000 at 01:03 UTC
    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]
RE:(2) Perl handling of a MYSQL bulletin board
by swiftone (Curate) on Aug 31, 2000 at 22:29 UTC
    I don't understand how this will allow (near)infinite-depth nesting. Am I missing something?
[reply]

In Section Seekers of Perl Wisdom