Page 1 of 1

Avoid Running Query on Home Page and Category Pages that fetches product By URL

Posted: Sat Oct 22, 2016 2:21 am
by tgugnani
When I open my home page and run 'Show processlist' in mysql I see this query is running

Code: Select all

select productimp0_.PRODUCT_ID as PRODUCT1_117_, productimp0_.ARCHIVED as ARCHIVED2_117_, productimp0_.CAN_SELL_WITHOUT_OPTIONS as CAN3_117_, productimp0_.DEFAULT_CATEGORY_ID as DEFAULT11_117_, productimp0_.DEFAULT_SKU_ID as DEFAULT12_117_, productimp0_.DISPLAY_TEMPLATE as DISPLAY4_117_, productimp0_.IS_FEATURED_PRODUCT as IS5_117_, productimp0_.MANUFACTURE as MANUFACT6_117_, productimp0_.MODEL as MODEL7_117_, productimp0_.OVERRIDE_GENERATED_URL as OVERRIDE8_117_, productimp0_.URL as URL9_117_, productimp0_.URL_KEY as URL10_117_, productimp0_1_.AUTO_BUNDLE as AUTO1_119_, productimp0_1_.BUNDLE_PROMOTABLE as BUNDLE2_119_, productimp0_1_.ITEMS_PROMOTABLE as ITEMS3_119_, productimp0_1_.PRICING_MODEL as PRICING4_119_, productimp0_1_.BUNDLE_PRIORITY as BUNDLE5_119_, productimp0_2_.ISBN10 as ISBN1_178_ case when productimp0_1_.PRODUCT_ID is not null then 1 when productimp0_2_.PRODUCT_ID is not null then 2 when productimp0_.PRODUCT_ID is not null then 0 end as clazz_ from BLC_PRODUCT productimp0_ left outer join BLC_PRODUCT_BUNDLE productimp0_1_ on productimp0_.PRODUCT_ID=productimp0_1_.PRODUCT_ID left outer join BOOKS_CATALOG productimp0_2_ on productimp0_.PRODUCT_ID=productimp0_2_.PRODUCT_ID cross join BLC_SKU skuimpl1_ where productimp0_.DEFAULT_SKU_ID=skuimpl1_.SKU_ID and (productimp0_.URL='/' or productimp0_.URL_KEY='/') and (skuimpl1_.ACTIVE_END_DATE>'2016-10-21 18:12:33' or skuimpl1_.ACTIVE_END_DATE is null) and (productimp0_.ARCHIVED is null or productimp0_.ARCHIVED='N')


This query is looking to find products which match the URL '/' (Home page URL)

This is the same query which runs to get Product detail via URL. I am not sure why this query is running on Home page and other category pages since there should'nt be any product maching category URL.

I was looking for controllers in broadleaf-web project, can you explain How is it distinguished which controller handleRequest is called when we call a category or a product URL.

Re: Avoid Running Query on Home Page and Category Pages that fetches product By URL

Posted: Tue Jul 25, 2017 10:51 am
by phillipuniverse
There are 3 handler mappings that Broadleaf provides for dynamic, database-driven URLs:

  • ProductHandlerMapping
  • CategoryHandlerMapping
  • SkuHandlerMapping (not hooked up by default)
  • PageHandlerMapping

Each of these are hooked up within applicationContext-servlet.xml (if you are on Broadleaf 5.1 or below) or SiteServletConfig on Broadleaf 5.2+.

Those queries also indicates that it is not cached. We do not have the misses (where it doesn't find a product) cached in community but we do in our commercial offerings.