Page 3 - MySQL

Indexer tables are the backbone of Magento performance on category and search pages because they replace EAV joins with precomputed, flat structures. Once you know the structure of catalog_category_product_index and catalog_product_index_price and how to place the Update on Save and Update on Schedule modes correctly, you can tune reindex runs deliberately instead of treating them as a black box.

InnoDB Cluster bundles Group Replication, the MySQL Shell AdminAPI, and MySQL Router into one integrated solution that delivers high availability without any additional third-party tools. Anyone who has already worked with Group Replication manually quickly notices how much operational complexity the AdminAPI actually removes, and where the limits of that automation sit.

CPU usage and free memory alone say almost nothing about the actual health of a MySQL database. Only buffer pool hit ratio, connection saturation, replication lag and lock waits together show whether a system is running stable or is about to hit an incident that classic server monitoring never sees.

Magento databases rarely grow because of product data, they grow because of logs, abandoned carts, and URL rewrites that nobody actively deletes. Once you know the usual suspects, measure table sizes regularly, and establish a repeatable cleanup routine, you prevent backups, deployments, and reindex runs from suffering under database growth.

The EAV schema lets Magento hold an unlimited number of product attributes without ALTER TABLE, but it pays for that flexibility with join overhead on every product query. Understanding the table structure of entity, attribute, and type specific value tables lets you tell real bottlenecks apart from imagined EAV problems, and fix them with flat tables or custom index tables where it actually matters.

When multiple users change the same record at the same time, the choice between Optimistic Locking and Pessimistic Locking determines whether your application reliably prevents lost updates or gets stuck waiting under load. This article explains both locking strategies in MySQL with real SQL and PHP code, walks through SELECT FOR UPDATE and version columns in detail, and helps you decide which model fits which use case.

Magento supports multiple websites, stores, and store views in a single database, but as the number of tenants grows, the question arises whether this multi-store schema still holds up or whether separate databases per tenant make a better architecture. The answer depends on attribute count, traffic distribution, and team structure, not on a blanket best practice.

Thousands of concurrent application connections overwhelm MySQL faster than the actual query load does. ProxySQL sits between application and database, pools connections through connection pooling and routes reads and writes to the right servers using query routing rules, without the application noticing anything.

Slow catalogs almost never result from too little hardware, but from queries without a matching index, unnecessarily wide SELECT statements and missing caching. This article shows how to read EXPLAIN plans, diagnose missing indexes, reduce connection overhead and configure MySQL/MariaDB for catalog-heavy PHP applications.

Before MySQL 8, a clean privilege model in practice usually meant running the same list of GRANT statements again for every single user, with all the risks of copy work that eventually drifts apart. Since MySQL 8, privileges can be defined once, centrally, as a role and then assigned to any number of users, including the option to make a role automatically mandatory at login or only active on request. This article covers the basic syntax, the difference between mandatory and optionally activated roles, and a practical privilege model for a team with a read only analyst, an app user, and a DBA on a shared Magento database.