[Performance/bug] UrlFinder::loadPageLookupArray causes constant DB lookups and unserialize warnings (Emico_AttributeLanding)

[gfrancisco-sh]

Summary

• The method \Emico\AttributeLanding\Model\UrlFinder::loadPageLookupArray() runs on every request and repeatedly executes a heavy join, leading to “stuck” queries and high DB load. Concurrently, unserialize() warnings
  from \Emico\AttributeLanding\Model\LandingPage surface during lookup building, suggesting caching is not being populated and forcing the SQL to run repeatedly.

Query

  SELECT
    main_table.*,
    emico_attributelanding_page_store.*
  FROM
    emico_attributelanding_page AS main_table
    INNER JOIN emico_attributelanding_page_store ON main_table.page_id = emico_attributelanding_page_store.page_id
  WHERE
    ((emico_attributelanding_page_store.active = 1))
    AND (
      (
        emico_attributelanding_page_store.is_filter_link_allowed = 1
      )
    );

---

Error/Stack Trace

Exception: Warning: unserialize(): Error at offset 53 of 77 bytes in /data/web/public/releases/22/vendor/emico/m2-attributelanding/src/Model/LandingPage.php on line 353 in /data/web/public/releases/22/vendor/magento/
framework/App/ErrorHandler.php:61
Stack trace:
#0 [internal function]: Magento\Framework\App\ErrorHandler->handler()
#1 /data/web/public/releases/22/vendor/emico/m2-attributelanding/src/Model/LandingPage.php(353): unserialize()
#2 /data/web/public/releases/22/vendor/emico/m2-attributelanding/src/Model/LandingPage.php(361): Emico\AttributeLanding\Model\LandingPage->getUnserializedFilterAttributes()
#3 /data/web/public/releases/22/vendor/emico/m2-attributelanding/src/Model/UrlFinder.php(153): Emico\AttributeLanding\Model\LandingPage->getFilters()
#4 /data/web/public/releases/22/vendor/emico/m2-attributelanding/src/Model/UrlFinder.php(84): Emico\AttributeLanding\Model\UrlFinder->loadPageLookupArray()
#5 /data/web/public/releases/22/vendor/tweakwise/magento2-attributelanding-tweakwise/src/Model/FilterManager.php(96): Emico\AttributeLanding\Model\UrlFinder->findUrlByFilters()
#6 /data/web/public/releases/22/vendor/tweakwise/magento2-attributelanding-tweakwise/src/Plugin/Block/LayeredNavigation/RenderLayered/RendererPlugin.php(57):
Tweakwise\AttributeLandingTweakwise\Model\FilterManager->findLandingPageUrlForFilterItem()
#7 /data/web/public/releases/22/vendor/magento/framework/Interception/Interceptor.php(146):
Tweakwise\AttributeLandingTweakwise\Plugin\Block\LayeredNavigation\RenderLayered\RendererPlugin->afterRenderAnchorHtmlTagAttributes()
#8 /data/web/public/releases/22/vendor/magento/framework/Interception/Interceptor.php(153):
Tweakwise\Magento2Tweakwise\Block\LayeredNavigation\RenderLayered\DefaultRenderer\Interceptor->{closure:Magento\Framework\Interception\Interceptor::___callPlugins():104}()
#9 /data/web/public/releases/22/generated/code/Tweakwise/Magento2Tweakwise/Block/LayeredNavigation/RenderLayered/DefaultRenderer/Interceptor.php(50):
Tweakwise\Magento2Tweakwise\Block\LayeredNavigation\RenderLayered\DefaultRenderer\Interceptor->___callPlugins()
#10 /data/web/public/releases/22/var/view_preprocessed/.../default.phtml(1): Tweakwise\Magento2Tweakwise\Block\LayeredNavigation\RenderLayered\DefaultRenderer\Interceptor->renderAnchorHtmlTagAttributes()
#11 /data/web/public/releases/22/vendor/magento/framework/View/TemplateEngine/Php.php(67): include('...')
#12 /data/web/public/releases/22/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Framework\View\TemplateEngine\Php->render()
#13 ...

Impact

• Severity: High (significant DB load; requests pile up under traffic).
• Scope: Layered navigation rendering (with Tweakwise integration).
• Frequency: Always; the query is executed on every request and may become “stuck”.

Steps To Reproduce

1. Use Emico AttributeLanding with Tweakwise integration enabled.
2. Visit a category page with layered navigation.
3. Observe repeated execution of the query above and unserialize() warnings in logs.
4. Repeat navigation or apply filters; the same query runs again instead of using a cache.

Actual Behavior

• loadPageLookupArray() is called frequently and appears not to leverage a persistent cache, re-querying emico_attributelanding_page and emico_attributelanding_page_store on each request.
• LandingPage::getUnserializedFilterAttributes() triggers unserialize() warnings during lookup building.

Expected Behavior

• The lookup should be cached (per store/scope) and reused across requests; after initial warm-up there should be no repeated DB hits.
• No unserialize() warnings should occur; invalid or legacy data should be handled gracefully.

Environment

• Magento: 2.4.8-p3 (exact version available on request)
• PHP: 8.4
• Emico AttributeLanding: v6.0.3
• Tweakwise AttributeLanding integration: v4.4.1
• DB: MySQL 8.4.5

[ahuininga-orisha]

@gfrancisco-sh

I can't reproduce this issue. I think it may be caused by certain pages / filters. Which causes the cache to not be created properly. Do you have an url where this always happens?

It can also be that the cache is created, but removed by another process. Can you check if the Observer/InvalidateCacheObserver is triggered repeatedly?

[ahuininga-orisha]

@gfrancisco-sh I can reproduce the unserialize error by using an multibyte character in my ALP filters. But this doesn't seem to trigger an cache problem.

So i think the unserialise error may be unrelated.