Posted by Naich on 10/20/05 16:12

On Wed, 19 Oct 2005, Stefan Rybacki wrote:

> NC wrote:
> > Naich wrote:
> >
> >>I'm going to be putting a comments section on my site, similar to
> >>something like Slashdot, i.e. nested comments in a tree structure.
> >>My question is - is it better to get the entire set of comments
> >>in one MySQL call and then use PHP to sort out the tree or would
> >>it be more efficient to go up the branches with multiple MySQL calls?
> >
> >
> > The first approach may result in high memory requirements, the second
> > will be much slower.
> >
> > The best approach woould be to figure out a database structure that
> > would allow you to retrieve all comments in one query in the correct
> > order, so no PHP sorting is required.

That's a possibility.

> The problem if such a structure is the overhead on inserting new data.
> And since he has a comment system, insertion could be very often. Don't
> get me wrong I would prefer such a structure but I'm not sure whether
> the OP get it to work or whether the insertion takes to long.

There would be far more retrievals than inserts, so I think this could be
the way to do it. Now to come up with some sort of cunning scheme...

Of course, a good way to do it would have been with stored procedures to
do all the leg work for the retreivals, using a simpler structure for
faster inserts, but that's not possible with the antique version of MySQL
I'm running.

Anyway, thanks to all for the advice.

Naich.
--
http://www.maggenhoof.co.uk/thoday . Improving our new dump.
http://www.veggiefoodguide.co.uk ... Time for some nice food.
http://www.sodwork.com ............. Right. I've had enough.
Motto: Don't you just hate rhetorical questions?

[Back to original message]