Adobe Commerce’s category listing pages-also known as Product Listing Pages (PLPs) are often the performance bottleneck in large-scale Adobe Commerce setups. As catalogs scale to tens or hundreds of thousands of SKUs, even basic interactions like filtering, pagination, and sorting can suffer from sluggish load times, high Time to First Byte (TTFB), and an overall clunky user experience.

Adobe Commerce PLP Performance Problems Due to SQL

Heavy Product Collection Queries

• Adobe Commerce’s default behavior is to load product IDs from Elasticsearch, but fetch full product data via MySQL.
• This includes joining multiple tables: catalog_product_entity, cataloginventory_stock_item, catalog_product_index_price, catalog_product_index_eav, etc.
• These queries are large and complex, especially when filters are applied.
• Impact: Sluggish page loads, high DB CPU usage, and slow TTFB.

Repeated EAV Table Joins

• Adobe Commerce stores product attributes in EAV (Entity Attribute Value) structure.
• For each attribute (e.g., color, size), a separate join is made with:
  • catalog_product_entity_varchar
  • catalog_product_entity_int
  • Catalog_product_entity_decimal
• Impact: Multiple joins per request significantly increase query execution time.

Stock & Pricing Queries for Each Product

• PLPs often display:
  • Tier pricing
  • Special pricing
  • Stock availability
• This triggers additional SQL reads from:
  • cataloginventory_stock_item
  • catalog_product_index_price
  • Cataloginventory_stock_status
• Impact: For 20-30 products per page, this creates dozens of extra SQL queries.

Slow Filtering & Sorting Operations

• Sorting & filtering by price, position, or custom attributes often translates into SQL-level operations on EAV attributes of large datasets.
• Impact: Slow sort = slow page.

Layered Navigation Filter Count Calculation

• Adobe Commerce calculates the number of matching products for each filter option (e.g., 15 for Red, 8 for Blue).
• This is calculated dynamically using SQL aggregation queries for each filterable attribute.
• Impact: Massive performance hit on stores with high attribute variability or large catalogs.

Image Processing / Media Gallery Joins

• Adobe Commerce can join with catalog_product_entity_media_gallery to fetch thumbnails or additional image attributes.
• Impact: Adds load unless image data is cached or preprocessed.

Lack of Proper Indexing

• Queries on non-indexed columns (e.g., custom attributes used in filters or sorting) can cause full table scans.
•  Impact: High I/O load, DB locking, and eventual TTFB spikes.

Third-party Extensions with Custom SQL

• Many extensions (e.g., advanced sorting, review count display, badges, etc.) execute raw SQL queries or observers/plugins that touch the DB.
• Impact: Each extension adds its own SQL logic, often outside Adobe Commerce’s indexing structure.

Other Adobe Commerce PLP Performance Problems 

No AJAX by Default

• Adobe Commerce does not support AJAX-based filters, pagination, or infinite scroll out of the box; it requires third-party modules.
• Impact: Poor user experience and higher bounce rates due to full page reloads.

SEO and Cache Fragility

• Uncached PLPs suffer from long TTFB. Adobe Commerce’s Full Page Cache (FPC) becomes ineffective when price, stock, or indexing updates trigger cache invalidation.
• Impact: Unpredictable performance, SEO ranking drops, and poor crawler experience.

High Developer Overhead

• Making UI/UX changes to PLPs requires navigating through multiple layout XML files, phtml templates, and frontend JS modules.
• Impact: Longer development cycles and higher chances of breaking dependent components.

What is Power Listing?

Power Listing is a custom-built Adobe Commerce 2 extension designed to:

Fetch complete listing page data directly from Elasticsearch, completely bypassing MySQL, and render it using a new lightweight frontend layer that includes AJAX and modern UX components out of the box.

Unlike Adobe Commerce’s core behavior, which offloads full data loading to MySQL, Power Listing extracts everything from Elasticsearch-including computed data like discount percentages, reviews, and more-resulting in faster performance and lower database load.

Power Listing is a high-performance Adobe Commerce 2 extension built to eliminate this problem at the root. By fully decoupling PLP data rendering from MySQL and utilizing Elasticsearch as the single source of truth, Power Listing offers blazing-fast performance with minimal compromise on flexibility, SEO, or developer experience.

How It Works

Traditional Flow:

User → Adobe Commerce → Elasticsearch (IDs) → MySQL (Product Data) → Rendering → User

Power Listing Flow:

User → Power Listing → Elasticsearch (Full Product Data) → Rendering → User

• No SQL queries
• No heavyweight PHP rendering logic
• Decoupled architecture allows leaner frontend rendering
  

Key Features

Full Elasticsearch Rendering

Fetches all product listing data-name, price, images, custom attributes, etc.-directly from Elasticsearch.

AJAX-Powered Interactions

Built-in support for:

• Filtering
• Sorting
• Pagination
  Minimal to zero reloads for seamless UX.
  

Configurable Infinite Scroll

Toggle between infinite scroll and traditional pagination via admin configuration.

Smart Reindexing Strategy

A two-phase indexing mechanism:

• Primary indexing: name, price, stock, visibility  indexed synchronously.
• Secondary indexing: reviews, discount %, image cache, etc.  Indexed asynchronously using RabbitMQ.
  

Plug-and-Play Setup

Works out of the box with most Adobe Commerce themes; minimal effort required to integrate.

Developer-Friendly Architecture

• Modular codebase
• Easily extendable to support custom attributes or 3rd-party data
• Lightweight theme overrides
  

Multi-Engine Compatibility

Though built with Elasticsearch in mind, Power Listing can work with any search engine (e.g., OpenSearch, Meilisearch) with minor adapter modifications.

Theme Flexibility

• Unlike the layered, rigid XML + phtml structure of Adobe Commerce’s default rendering, Power Listing uses a clean templating approach that can be customized easily.
  
• Easy support for:
  
  • PLP cards
  • Grid/list toggle
  • Custom sort options
  • Conditional labels (e.g., Sale, Out of Stock)

Conclusion

Power Listing is a strategic enabler for high-scale Adobe Commerce storefronts. By eliminating the need for MySQL in listing pages and delivering a modern, AJAX-native experience out of the box, it helps brands drastically reduce load times, improve SEO reliability, and enable fast, flexible UX customization. Whether you’re struggling with TTFB, development delays, or cache fragility, Power Listing provides a scalable and future-ready foundation for optimized catalog performance.