[TYPO3-dev] Typo3 Performance

[R. van Twisk]

Martin Kutschker wrote:
> Dmitry Dulepov schrieb:
>   
>> Hi!
>>
>> Martin Kutschker wrote:
>>     
>>> Can you explain this model in short or give me a pointer to an online 
>>> resource?
>>>       
>> This model was proposed by Oracle DB expert Joe Celko. Generally it 
>> marks branches of the tree with consecutive numbers so that when you 
>> want to fetch root path or get the whole branch, you just need to issue 
>> query with two numbers. Example:
>>
>>                     |- C (3,4)
>>         |- B (2, 7)-|
>> A(1,14)-|           |- D (5,6)
>>         |           |
>>         |- E (8,13)-|- F (9,10)
>>                     |
>>                     |- G (11,12)
>>     
>
> Quite interesting.
>
> I notice that several articles on this topic suggest to use table locks 
> (for Myisam) or transactions when updating the table. This makes sense 
> but forces us to deal with a Myisam vs Innodb issue plus a DBAL issue.
>
> Still it's interesting to see which features of TYPO3 can be sped up 
> with queries on the nested sets.
>
> But I wonder what will happen if you have thousands of pages. Maybe it 
> makes sense to have a separate set for each root page (ie a page with 
> pid=0). So changes in one domain won't affect other domains.
>
> Masi
> _______________________________________________
>   


Actually,

the current implementation I have does that already, but on a other 
database set.
However currently i feel for typo3 it really doesn't matter that each 
root (pid=0)
starts with leftnode=1. When we would do that is only creates an additional
where clause in the update which we need to avoid.

Basically every node will start with a un-even number. Here is an 
example how a two
root-page node can look like.



                         |- C1 (17,18)
          |- B1 (16, 21)-|
A1(15,28)-|              |- D1 (19,20)
          |              |
          |- E1 (22,27)- |- F1 (23,24)
                         |
                         |- G1 (25,26)

                    |- C (3,4)
        |- B (2, 7)-|
A(1,14)-|           |- D (5,6)
        |           |
        |- E (8,13)-|- F (9,10)
                    |
                    |- G (11,12)



I hope I did the calculation correctly...

To select all page below page B1 The query simply looks for all pages
between 16 and 21 which will result in page B1, C1 and D1.
This is handy to find all pages below page X with some properly...

As you can see, adding a page, or moving is expensive, the following 
schema can be
used to make it less expensive..

You simply provide gaps, below I added a gap on page G

                    |- C (3,4)
        |- B (2, 7)-|
A(1,24)-|           |- D (5,6)
        |           |
        |- E (8,23)-|- F (9,10)
                    |
                    |- G (11,22)

As you can see the left and right node are not anymore following this 
rule (leftnode=rightnode+1)
But a other rule could be applied to it.
Now when adding a page below G it goes like this:

                    |- C (3,4)
        |- B (2, 7)-|
A(1,24)-|           |- D (5,6)
        |           |
        |- E (8,23)-|- F (9,10)
                    |
                    |- G (11,22) - H (12,21)

As you can see the nodes are not updated in the tree at all,
and simply a page is added after the GAP.
This only makes it a bit more hard to find leaf nodes.
Currently I feel we should keep using the adjacency list model
and use that together with a experimental extension for creating leafnodes.

What I can do is created this extension:
tx_nestedset_pages -> add and maintains a nested set model on the pages 
table.
We could then also create tx_nestedset_ttnews to add the same to news 
tables and other tables.
The extension will be by no means optimized but I think it will work 
fast in the BE
even for a large number of pages (I cannot define large yet.....).

Then other developers can come in and play and fool around with that.

Ries