Too many pages makes MySQL run out RAM trying to build the autonav
Permalink
So, my site has over 7k pages. Most of them have the exclude from auto nav attribute set, but the way the autonav controller is programed, it doesn't recognize said attribute and just selects them all, and deals with it later.
I went through the trouble of creating a select query that DIDN'T crash MySQL every time the autonav was built. Here it is, I hope someone else can benefit from this:
I went through the trouble of creating a select query that DIDN'T crash MySQL every time the autonav was built. Here it is, I hope someone else can benefit from this:
$q = "select Pages.cID from Pages where cIsTemplate = 0 and cIsActive = 1 and cParentID = '{$cParentID}' and Pages.cID not in (select a.cID from ( select cID, max(avID) as avID from CollectionAttributeValues where akID= (SELECT akID FROM AttributeKeys WHERE akHandle = 'exclude_nav') group by cID) as a, atBoolean where a.avID=atBoolean.avID and atBoolean.value='1') $orderBy";
It seems that there is a misunderstanding somewhere, here is the query that I edited:
From the way I read the code, as long as the method "getNavigationArray" is called on a parentID (that in my case has 7k child pages), it looks at every one of the parent's sub pages no matter what attribute is set.
The only way for me to get around this is if I use the "Display None" functionality on the block... so it doesn't look for children of any of the parent pages on the nav bar. The only problem is that I want some subpages to show on other navbar items, so this isn't a desired result.
The manual nav doesn't appear to have the ability to choose any sub pages that I do want to show up, so it doesn't fit my needs either.
The query that I posted above works... but it's slow. I think I'm going to use the autonav block, but display none of the subpages, then create a template for it that contains any subpages I do want to show. I don't appreciate hardcoding it, but I think the extra speed will be worth it.
q = "select Pages.cID from Pages where cIsTemplate = 0 and cIsActive = 1 and cParentID = '{$cParentID}' {$orderBy}";
From the way I read the code, as long as the method "getNavigationArray" is called on a parentID (that in my case has 7k child pages), it looks at every one of the parent's sub pages no matter what attribute is set.
The only way for me to get around this is if I use the "Display None" functionality on the block... so it doesn't look for children of any of the parent pages on the nav bar. The only problem is that I want some subpages to show on other navbar items, so this isn't a desired result.
The manual nav doesn't appear to have the ability to choose any sub pages that I do want to show up, so it doesn't fit my needs either.
The query that I posted above works... but it's slow. I think I'm going to use the autonav block, but display none of the subpages, then create a template for it that contains any subpages I do want to show. I don't appreciate hardcoding it, but I think the extra speed will be worth it.
I think your solution to write your own query is the only way to go. My understanding is there are two reasons why it was built the way it was built (that is, why "exclude_from_nav" is utilized *after* the query takes place):
1) because it was a feature added after the original system, so they didn't want to break old sites
2) because it was intended to work that way -- some templates do not want to honor that attribute (for example, you might want a breadcrumb nav menu to always show the breadcumb trail to the current page, even if one of those parent pages is "exclude from nav")
That being said, I agree that it would be nice to have that be utilized deeper down in the autonav logic... there are a lot of things about the autonav block that are not ideal actually, but I think it's so widely used in so many sites that they are afraid to break anything.
I personally have a dream of one day having a super-duper navigation block that combines the features of AutoNav with Page List (because it's so hard to understand or explain the difference between them), but runs much more efficiently and has a nicer editing UI. I doubt that will ever happen, but a man can dream, right?
1) because it was a feature added after the original system, so they didn't want to break old sites
2) because it was intended to work that way -- some templates do not want to honor that attribute (for example, you might want a breadcrumb nav menu to always show the breadcumb trail to the current page, even if one of those parent pages is "exclude from nav")
That being said, I agree that it would be nice to have that be utilized deeper down in the autonav logic... there are a lot of things about the autonav block that are not ideal actually, but I think it's so widely used in so many sites that they are afraid to break anything.
I personally have a dream of one day having a super-duper navigation block that combines the features of AutoNav with Page List (because it's so hard to understand or explain the difference between them), but runs much more efficiently and has a nicer editing UI. I doubt that will ever happen, but a man can dream, right?
The answer I came up with made me want to slap myself due to it's simplicity. All the pages that I was creating were due to 6 years of generating, digital, products that were available for download. I, simply, moved the products parent page out of the nav, and created a new page with a product list block in its place. DONE.
I noticed that bigger sites got really slow because of the navigation and was looking for a solution.
I found a call that speeds it up similar to yours. It is even faster but has the drawback that if you place any "Alias" in the navigation it will not be shown. Still listing it here as it may help someone (don't remember who wrote it, but credit goes to him):
I found a call that speeds it up similar to yours. It is even faster but has the drawback that if you place any "Alias" in the navigation it will not be shown. Still listing it here as it may help someone (don't remember who wrote it, but credit goes to him):
$q = "select Pages.cID from Pages left join CollectionSearchIndexAttributes Attributes on Pages.cID = Attributes.cID where cIsTemplate = 0 and cIsActive = 1 and cParentID = '{$cParentID}' and Attributes.ak_exclude_nav <> 1 {$orderBy}
There is also an 'exclude all pages below' attribute (now built in on c5.6, an addon on earlier c5) which may help limit unwanted searching of subpages when building the menu.
Finally, there is a free manual nav addon, so you tell it what pages rather than letting it search for them.