Magento MySQL hanging

Magento MySQL hanging

August 21, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento MySQL hanging

MySQL is often used as the database management system for Magento e-commerce platforms due to its robustness, scalability, and wide community support. However, when it comes to handling large datasets or high-traffic stores, MySQL can sometimes become a bottleneck. One of the key factors that can contribute to slow performance in Magento 2 is the excessive number of database queries executed during each page load.

In your case, the Magento 2 log shows an unusually high volume of queries being executed for a single product view – over 100 queries in total. This excessive query execution can lead to significant delays in loading the product detail page for customers, which can negatively impact user experience and potential conversions.

To address this issue, we need to identify the specific queries that are causing the slowdowns. From the logs provided, it appears that a significant portion of these queries is related to retrieving customer address data (customer_address_entity_varchar), product website association information (catalog_product_website), and category details (catalog_category_entity_varchar). These queries seem to be executed multiple times for each product view.

Optimizing database query performance in Magento 2 involves several strategies:

1. Enable Indexing

The database tables involved in these heavy queries – customer_address_entity_varchar, catalog_product_website, and catalog_category_entity_varchar – should have appropriate indexes defined on the columns used in the WHERE clauses of the queries (entity_id, attribute_id, store_id). Having proper indexing ensures that the database can quickly locate the required data, reducing query execution time.

You can verify if the indexes exist by running the following SQL commands:

SHOW INDEXES IN customer_address_entity_varchar;
SHOW INDEXES IN catalog_product_website;
SHOW INDEXES IN catalog_category_entity_varchar;

If the necessary indexes are missing, you can create them using the following SQL statements:

CREATE INDEX idx_customer_address_entity_varchar_attribute_id ON customer_address_entity_varchar (attribute_id);
CREATE INDEX idx_catalog_product_website_product_id ON catalog_product_website (product_id);
CREATE INDEX idx_catalog_category_entity_varchar_attribute_id ON catalog_category_entity_varchar (attribute_id);

It's important to note that creating indexes can improve query performance but may impact the speed of data modification operations such as INSERT, UPDATE, and DELETE. Therefore, it is crucial to strike a balance between indexing for faster queries and maintaining efficient data modification performance.

2. Use EAV Optimization

The Entity-Attribute-Value (EAV) design pattern used in Magento 2 allows for flexibility but can lead to performance overhead when dealing with a large number of attributes or attribute options. To optimize the EAV-related queries, you can consider implementing EAV optimization techniques like denormalization or attribute value caching.

Denormalization involves storing frequently accessed attribute values directly in the main table (e.g., catalog_product_entity) instead of using separate tables. This reduces the need for multiple JOIN operations and can improve query performance. However, it comes with trade-offs such as increased storage requirements and complexity in managing data consistency.

Attribute value caching involves storing frequently accessed attribute values in a cache layer like Memcached or Redis to minimize database queries. By caching the attribute values, subsequent requests can retrieve the data from the cache instead of querying the database, resulting in faster response times.

Implementing EAV optimization requires careful analysis of your specific use case and understanding the trade-offs involved. It is recommended to consult with a database expert or Magento performance specialist to determine the best approach for your particular scenario.

3. Optimize Category Retrieval

The category retrieval queries seem to be executed multiple times in your log, which can contribute to the overall slowdown of the product detail page loading process. To optimize category retrieval:

  • Ensure that only relevant categories are retrieved for a given product.
  • Use appropriate caching mechanisms to store frequently accessed category data.
  • Consider implementing lazy loading or pagination for category lists if the number of categories is very large.

The Magento 2 system provides built-in functionality like product-category-relations and category hierarchy management that can help optimize category retrieval. Make sure these features are properly configured and utilized in your store setup.

4. Optimize Database Settings

Optimizing MySQL configuration settings can also contribute to improved database performance. Some key configurations to consider include:

  • Increase the number of connections allowed by adjusting the max_connections and wait_timeout variables in the my.cnf file.
  • Configure appropriate values for table cache size (table_cache), query cache size (query_cache_size), and join buffer size (join_buffer_size) based on your server's available memory.
  • Optimize MySQL query caching by enabling it if not already enabled and adjusting the query cache settings appropriately.

Remember to monitor the database performance metrics regularly using tools like MySQL Performance Schema or external monitoring solutions. This will help identify any potential bottlenecks or areas for optimization in your MySQL configuration.

Conclusion

Optimizing Magento 2 database query performance requires a systematic approach that involves analyzing the specific queries causing slowdowns, understanding the underlying architecture of the system, and implementing appropriate optimizations based on your store's needs. In this case, enabling proper indexing, optimizing EAV-related queries, improving category retrieval efficiency, and fine-tuning MySQL configuration settings can help address the excessive query execution issue you are facing.

It is crucial to work with a Magento performance specialist or database expert who has experience in optimizing e-commerce platforms like yours. They can provide guidance on specific optimizations tailored to your store's setup and help ensure that any changes made do not introduce unintended consequences.

By collaborating with professionals from Laravel Company, you can leverage their expertise in Magento 2 optimization, database performance tuning, and infrastructure management. They offer a wide range of services to enhance the speed, security, and scalability of your e-commerce platform.

Contact them today to schedule a consultation or request assistance with optimizing your Magento 2 store's database query performance. Their team of experienced developers can provide personalized solutions that meet your specific requirements and help you deliver an exceptional user experience to your customers.

Remember, optimizing the database is just one aspect of improving overall Magento 2 performance. It is essential to consider other factors such as server configuration, caching mechanisms, code optimization, and infrastructure scaling to achieve optimal results for your e-commerce business.

Feel free to reach out to Laravel Company for any further assistance or inquiries regarding Magento 2 performance optimization. They are committed to providing high-quality solutions that drive success for their clients in the world of e-commerce.

Magento 2 Catalog Indexing Database

Our network