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

[Ries van Twisk]

On Apr 9, 2007, at 3:47 AM, Martin Kutschker wrote:

> 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).

Indeed, since the connection is shared this might be a problem,
Normally a TEMP table is created globally in the DB
according to ANSI SQL (if I remember correctly).


>
>> 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).
it is, it's knowledge.....

>
> 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).
Still a SP could be used that know about mountpoints.
And in combination with nested set's we do less calls to the DB engine
and the Query optimizer and we do need less connections to
the DB.

I think we can start to see better advantages when the DB is under  
stress anyways.


However,
a nested set can also be solved for questions like:

- Give me the latest 10 tt_content records under some page.


Just some thoughts...

Ries