Task #99157
Updated by Sybille Peters over 1 year ago
Loading sitemap.xml seemed slow, had load time of more than 20s (see measurements below of over a minute for). 20s. - the cache duration is by default 900 (15 minutes) which seems very short as the average page cache time is 24h. While having a fresh sitemap is of course prefereable. But, due to the long load time, this means the sitemap is effectively broken on large sites, until it is warmed up again. (the cache duration can of course be overridden but it remains the default) - There is an SQL fetch with select * (all fields) . It is probably not necessary to fetch all the fields. <pre><code class="sql"> SELECT * FROM `pages` WHERE (`uid` IN (57119 ...)) AND (no_index = 0) AND (`doktype` NOT IN (3, 4, 6, 7, 199, 254, 255)) AND ((`pages`.`deleted` = 0) AND (`pages`.`hidden` = 0) AND (`pages`.`starttime` <= 1669052220) AND ((`pages`.`endtime` = 0) OR (`pages`.`endtime` > 1669052220))) ORDER BY `uid` ASC </code></pre> This alone does not contribute to the long load time, but it might be possible to make some of the SQL queries more efficient. h2. System - TYPO3 11.5.19 - pages in sitemap (default language): ~25000 - also news in sitemap (default language): ~15000 h2. Measurements Feching the individual sitemaps for translated pages seems especially slow. After a full cache flush: * URL (for page 5): https://mysite/en/sitemap.xml?page=5&sitemap=pages&cHash=179f57abab819f22b0581465651db76d * Time: *1 minute, 26 seconds* (default language is German, English is translation) h2. DB queries Measurement SELECT * FROM `pages` WHERE (`uid` IN (57119 ...)) AND (no_index = 0) AND (`doktype` NOT IN (3, 4, 6, 7, 199, 254, 255)) AND ((`pages`.`deleted` = 0) AND (`pages`.`hidden` = 0) AND (`pages`.`starttime` <= 1669052220) AND ((`pages`.`endtime` = 0) OR (`pages`.`endtime` > 1669052220))) ORDER BY `uid` ASC; h2. Reproduce To see the SQL, just make an error in additionalWhere TypoScript on development system which will show the exception with the DB query plugin.tx_seo.config.xmlSitemap.sitemaps.pages.config.additionalWhere = {#no_index} = 0 AND {#canonical_link} = '' ORDER BY blah