MySQL Query Optimization for Large Magento 2 Catalogs
When Your Catalog Outgrows Default Configuration
Magento 2 stores with catalogs exceeding 100,000 products often experience degraded performance as the EAV data model generates complex multi-join queries. Category pages, layered navigation, and admin grids become sluggish. Understanding how Magento interacts with MySQL is the first step toward targeted optimization.
Identifying Problematic Queries
Enable the MySQL slow query log with a threshold of one second. Use tools like pt-query-digest from the Percona Toolkit to aggregate and rank slow queries by total execution time. Common culprits include catalog price indexing queries, flat table rebuild operations, and URL rewrite generation for stores with many categories and products.
Indexing and Configuration Strategies
Ensure the Magento flat catalog indexers are enabled for both categories and products. Tune InnoDB buffer pool size to accommodate your working dataset—typically 70-80% of available RAM on a dedicated database server. Consider partitioning the sales_order and quote tables by date range for stores with high transaction volumes. For read-heavy workloads, a MySQL replica configured as a read-only slave can offload reporting and search indexing queries from the primary server.