TYPO3 Core

php version = 5.2.13
mysql version = 5.0.86
BTW

The same happens in version 4.2.10 btw. using only the TS below:

page = PAGE
page {
typeNum = 0
headerData.10 = TEXT
headerData.10.value = <link href="/fileadmin/templates/layout.css" rel="stylesheet" type="text/css"/>
10 = TEMPLATE
10 {
template = FILE
template.file = fileadmin/templates/main2.html

subparts.MAINMENU = COA
        subparts.MAINMENU {
            wrap = <ul>|</ul>        
            10 = CONTENT
            10 {
                table = pages
                select.selectFields = title as title, uid
                select.pidInList = 92
                renderObj = COA
                renderObj {
                    10 = TEXT
                    10.field = title
                    10.wrap = <li>|</li>
                    10.typolink.parameter.field = uid

20 = CONTENT
                    20 {
                        table = pages
                        select.pidInList.field = uid
                        select.selectFields = title as cow, uid as chicken
                        renderObj = TEXT
                        renderObj {
                            field = cow
                            wrap = <li>|</li>
                            typolink.parameter.field = chicken
                        }
                    }
                }
            }
        }
    }
}

generates mySQL queries like below, whereby 2 extra queries are generated with every result :
SELECT uid FROM pages WHERE uid IN (13659) AND pages.deleted=0 AND pages.t3ver_state<=0 AND pages.hidden=0 AND pages.starttime<=1283779920 AND (pages.endtime=0 OR pages.endtime>1283779920) AND (pages.fe_group='' OR pages.fe_group IS NULL OR pages.fe_group='0' OR (pages.fe_group LIKE '%,0,%' OR pages.fe_group LIKE '0,%' OR pages.fe_group LIKE '%,0' OR pages.fe_group='0') OR (pages.fe_group LIKE '%,-1,%' OR pages.fe_group LIKE '-1,%' OR pages.fe_group LIKE '%,-1' OR pages.fe_group='-1')) AND doktype NOT IN (255)
SELECT title as cow, uid as chicken FROM pages WHERE pages.pid IN (13659) AND pages.deleted=0 AND pages.hidden=0 AND pages.starttime<=1283779920 AND (pages.endtime=0 OR pages.endtime>1283779920) AND NOT pages.t3ver_state>0 AND pages.doktype<200 AND (pages.fe_group='' OR pages.fe_group IS NULL OR pages.fe_group='0' OR (pages.fe_group LIKE '%,0,%' OR pages.fe_group LIKE '0,%' OR pages.fe_group LIKE '%,0' OR pages.fe_group='0') OR (pages.fe_group LIKE '%,-1,%' OR pages.fe_group LIKE '-1,%' OR pages.fe_group LIKE '%,-1' OR pages.fe_group='-1'))
SELECT * FROM pages WHERE uid=13662 AND pages.deleted=0
SELECT * FROM pages WHERE uid=13662 AND pages.deleted=0 AND pages.hidden=0 AND pages.starttime<=1283779920 AND (pages.endtime=0 OR pages.endtime>1283779920) AND NOT pages.t3ver_state>0 AND pages.doktype<200 AND (pages.fe_group='' OR pages.fe_group IS NULL OR pages.fe_group='0' OR (pages.fe_group LIKE '%,0,%' OR pages.fe_group LIKE '0,%' OR pages.fe_group LIKE '%,0' OR pages.fe_group='0') OR (pages.fe_group LIKE '%,-1,%' OR pages.fe_group LIKE '-1,%' OR pages.fe_group LIKE '%,-1' OR pages.fe_group='-1'))
SELECT * FROM pages WHERE uid=13674 AND pages.deleted=0
SELECT * FROM pages WHERE uid=13674 AND pages.deleted=0 AND pages.hidden=0 AND pages.starttime<=1283779920 AND (pages.endtime=0 OR pages.endtime>1283779920) AND NOT pages.t3ver_state>0 AND pages.doktype<200 AND (pages.fe_group='' OR pages.fe_group IS NULL OR pages.fe_group='0' OR (pages.fe_group LIKE '%,0,%' OR pages.fe_group LIKE '0,%' OR pages.fe_group LIKE '%,0' OR pages.fe_group='0') OR (pages.fe_group LIKE '%,-1,%' OR pages.fe_group LIKE '-1,%' OR pages.fe_group LIKE '%,-1' OR pages.fe_group='-1'))
SELECT * FROM pages WHERE uid=13677 AND pages.deleted=0
SELECT * FROM pages WHERE uid=13677 AND pages.deleted=0 AND pages.hidden=0 AND pages.starttime<=1283779920 AND (pages.endtime=0 OR pages.endtime>1283779920) AND NOT pages.t3ver_state>0 AND pages.doktype<200 AND (pages.fe_group='' OR pages.fe_group IS NULL OR pages.fe_group='0' OR (pages.fe_group LIKE '%,0,%' OR pages.fe_group LIKE '0,%' OR pages.fe_group LIKE '%,0' OR pages.fe_group='0') OR (pages.fe_group LIKE '%,-1,%' OR pages.fe_group LIKE '-1,%' OR pages.fe_group LIKE '%,-1' OR pages.fe_group='-1'))

SELECT uid FROM pages WHERE uid IN (13677) AND pages.deleted=0 AND pages.t3ver_state<=0 AND pages.hidden=0 AND pages.starttime<=1283779920 AND (pages.endtime=0 OR pages.endtime>1283779920) AND (pages.fe_group='' OR pages.fe_group IS NULL OR pages.fe_group='0' OR (pages.fe_group LIKE '%,0,%' OR pages.fe_group LIKE '0,%' OR pages.fe_group LIKE '%,0' OR pages.fe_group='0') OR (pages.fe_group LIKE '%,-1,%' OR pages.fe_group LIKE '-1,%' OR pages.fe_group LIKE '%,-1' OR pages.fe_group='-1')) AND doktype NOT IN (255)
SELECT title as cow, uid as chicken FROM pages WHERE pages.pid IN (13677) AND pages.deleted=0 AND pages.hidden=0 AND pages.starttime<=1283779920 AND (pages.endtime=0 OR pages.endtime>1283779920) AND NOT pages.t3ver_state>0 AND pages.doktype<200 AND (pages.fe_group='' OR pages.fe_group IS NULL OR pages.fe_group='0' OR (pages.fe_group LIKE '%,0,%' OR pages.fe_group LIKE '0,%' OR pages.fe_group LIKE '%,0' OR pages.fe_group='0') OR (pages.fe_group LIKE '%,-1,%' OR pages.fe_group LIKE '-1,%' OR pages.fe_group LIKE '%,-1' OR pages.fe_group='-1'))
SELECT * FROM pages WHERE uid=13678 AND pages.deleted=0
SELECT * FROM pages WHERE uid=13678 AND pages.deleted=0 AND pages.hidden=0 AND pages.starttime<=1283779920 AND (pages.endtime=0 OR pages.endtime>1283779920) AND NOT pages.t3ver_state>0 AND pages.doktype<200 AND (pages.fe_group='' OR pages.fe_group IS NULL OR pages.fe_group='0' OR (pages.fe_group LIKE '%,0,%' OR pages.fe_group LIKE '0,%' OR pages.fe_group LIKE '%,0' OR pages.fe_group='0') OR (pages.fe_group LIKE '%,-1,%' OR pages.fe_group LIKE '-1,%' OR pages.fe_group LIKE '%,-1' OR pages.fe_group='-1'))
SELECT * FROM pages WHERE uid=13710 AND pages.deleted=0
SELECT * FROM pages WHERE uid=13710 AND pages.deleted=0 AND pages.hidden=0 AND pages.starttime<=1283779920 AND (pages.endtime=0 OR pages.endtime>1283779920) AND NOT pages.t3ver_state>0 AND pages.doktype<200 AND (pages.fe_group='' OR pages.fe_group IS NULL OR pages.fe_group='0' OR (pages.fe_group LIKE '%,0,%' OR pages.fe_group LIKE '0,%' OR pages.fe_group LIKE '%,0' OR pages.fe_group='0') OR (pages.fe_group LIKE '%,-1,%' OR pages.fe_group LIKE '-1,%' OR pages.fe_group LIKE '%,-1' OR pages.fe_group='-1'))
SELECT * FROM pages WHERE uid=13712 AND pages.deleted=0
SELECT * FROM pages WHERE uid=13712 AND pages.deleted=0 AND pages.hidden=0 AND pages.starttime<=1283779920 AND (pages.endtime=0 OR pages.endtime>1283779920) AND NOT pages.t3ver_state>0 AND pages.doktype<200 AND (pages.fe_group='' OR pages.fe_group IS NULL OR pages.fe_group='0' OR (pages.fe_group LIKE '%,0,%' OR pages.fe_group LIKE '0,%' OR pages.fe_group LIKE '%,0' OR pages.fe_group='0') OR (pages.fe_group LIKE '%,-1,%' OR pages.fe_group LIKE '-1,%' OR pages.fe_group LIKE '%,-1' OR pages.fe_group='-1'))
SELECT * FROM pages WHERE uid=13856 AND pages.deleted=0

... and the list goes on ...

Maybe you could / should clarify what exactly you are trying to acchieve with this nested call of "CONTENT"?

ok, my apologies if this explanation seems a bit confusing.

I only want to create a simple nested menu of main pages and their subpages, with HTML output like below, but the regular HMENU seems to generate many redundant queries with our typo3 test installation. The database of this installation contains about 200.000 records, a small portion of the entire database we're about to import. After the first import, we noticed loading times went up. A non-cached page took 20+ seconds to load, so we turned the mysql query log on and counted about 40.000+ queries per non cached page.

With the code above I only wanted to illustrate that an alternative like a nested CONTENT element seems to amount to the same result.

<ul>
    <li>
        <a href="#">main menuitem 1</a>
        <ul>
            <li><a href="#">sub menuitem 1.1</a></li>
            <li><a href="#">sub menuitem 1.2</a></li>
            <li><a href="#">sub menuitem 1.3</a></li>
        </ul>
    </li>
    <li>
        <a href="#">main menuitem 2</a>
        <ul>
            <li><a href="#">sub menuitem 2.1</a></li>
            <li><a href="#">sub menuitem 2.2</a></li>
        </ul>
    </li>
    <li>
        <a href="#">main menuitem 3</a>
        <ul>
            <li><a href="#">sub menuitem 3.1</a></li>
            <li><a href="#">sub menuitem 3.2</a></li>
            <li><a href="#">sub menuitem 3.3</a></li>
            <li><a href="#">sub menuitem 3.4</a></li>
        </ul>
    </li>
</ul>

I meant

<ul><li>main menuitem 1<ul><li>sub menuitem 1.1</li><li>sub menuitem 1.2</li><li>sub menuitem 1.3</li></ul></li><li>main menuitem 2<ul><li>sub menuitem 2.1</li><li>sub menuitem 2.2</li></ul></li></ul>

your html gets destroyed in the bug tracker. Maybe you could paste your HTML snippets to somewhere like http://pastebin.com/ and embedd the URL here?

thx Ernesto!

http://pastebin.com/embed_js.php?i=brsVDr8G

You have 200.000 pages in your tree? Or what do you mean with "records"? How nested are the records of the tree? How many menuitems do you expect on one particular page?

With records I meant "Database records'

records in 'pages' table = 24,691
records in tt_content = 110,390
records in tt_news = 16,543

+ all others

The test installation contains 12 websites at this moment with different menustructures. Nesting differs per website and there are many levels of pages per website, so I can't give the exact number, but an average main menu contains 8 mainitems, each containing about 8 subitems.

How did your original menu look(with hmenu/tmenu) like?

ok, there you are:

http://pastebin.com/embed_iframe.php?i=T5Zfu5fR

deleting objects with levels 3 lowers the number of queries significanty. From 40.000 to about 4000. That's still too many, though.

menu.10 {
3 >
4 >
5 >
6 >
}

When I delete the menu entirely. The total number of queries sticks to about 700.

menu >

A tree with 6 levels, with each branch containing 8 sub-branches has:
8 subpages on level 1 (each to be queried for expAll)
8*8=64 on level 2
8*8*8=512 on level 3
8*8*8*8=4096.0 on level 4
8*8*8*8*8=32768.0 on level 5
8*8*8*8*8*8=262144.0 on level 6

so in this case the number of queries looks normal.
Do you have many shortcut-pages or mount points in use?

You write something about 12 Websites in your page tree. Is the root flag set correctly everywhere? otherwise with entryLevel=0, you wolud always traverse your whole tree.

hi Bjorn,

thank you for you reply.

I understand the multiplying logic behind it. The point I'm trying to make is that (perhaps just in my case) a 2 level menu does a 4 level number of queries. That's why I stripped down the installation to a bare minimum and analyzed the queries that were executed by mySQL. It seems that, for each query you define in TS on the pages db, typo3 somehow adds 2 extra queries for each result. I'm just wondering why or if anyone else has had the same experience.

the rootlevel is set correctly btw.

Hi Rutger,

Somehow there is a farm in your installation. I'm not kidding ;-) And I don't mean a server farm.

Take a look at your own queries you've posted. There is the following part multiple times:

SELECT title as cow, uid as chicken FROM pages

That's something which is not from the core of TYPO3. We're not abusing animals.

Looks like an extension is using a hook somewhere.

hi Patrick,

thx for the response. I've also tested this with a clean setup (versions 4.1.1 and 4.2.1.) with only some minimal TS. http://pastebin.com/FNYSYTEq. again same results

Don't mean to offense ... but, It seems like a core thingy to me :(

The animals are in there to track the sql queries :)