[TYPO3-dev] FYI nested sets on the pages table

[Martin Kutschker]

Ries van Twisk schrieb:
> Hey martin,
> 
> would the SP not be dangerous ?
> 
> The table get's created and used in a multi user environment
> so under some conditions I think the values of the table
> get's overwritten but other values.

No, a temporary table is visible only to to the current DB session. But 
you are right when it comes to connection pooling. In this case the 
table is a problem (but of course this may be solved by adding an extra 
id field that is filled with a random key).

> You could modify the SP below using the nested set's fields
> which will directly give you the correct sorting which
> will eliminate the need for the temporary table.

But I can get the rootline with one query with a nested set, right? I 
was just playing around because someone posted somewhere (lost the link) 
a benchmark where the rootline query for a nested set was slower then 
multiple queries for the parent model. I guess the join makes its slower 
  and the patent model has the better index.

So this is just a show case.

> In any case... it's a step forward...

Not really. Stroed procedures are not (easily) portable, so maintainign 
them with DBAL could be a problem (and you need Mysql 5.0 anyway).

Masi

PS: The procedure won't work as-is anyway because of mount points!!! And 
I think that a nested set will perform worse with mount points as you 
get more unwanted data (the join overhead will kill any advantage).