Page 3 - MySQL

Generated columns automatically compute a value from other columns in the same row and, unlike plain expressions in a WHERE clause, can be indexed directly. This article explains the difference between STORED and VIRTUAL, shows indexing strategies, and covers concrete use cases for JSON extraction and computed totals in MySQL 8.

InnoDB is considered a row-level locking engine, yet table locks regularly occur in practice, for example during ALTER TABLE or explicit LOCK TABLES. This article explains how InnoDB technically implements row locks, when table locking becomes unavoidable, and how the lock modes S, X, IS and IX work together as intention locks.

The InnoDB buffer pool decides whether a query is answered from memory in microseconds or triggers a slow disk operation. Anyone who sizes innodb_buffer_pool_size incorrectly or misunderstands the LRU list gives up performance that no index tuning can recover. This article explains the buffer pool's structure, sizing methodology and monitoring with real SQL examples.

Log tables like report_event, customer_visitor, and catalog_compare_item log visitor behavior and grow with every page view, often faster than any other table group in the store. Once you configure built-in log cleaning correctly and build custom scripts for tables without automatic cleanup, you keep the database lean without giving up important reporting data.

EXPLAIN shows what the optimizer plans, EXPLAIN ANALYZE shows what actually happened. Anyone who correctly interprets type, key, rows and Extra, and compares estimated rows against real execution statistics, finds full table scans and filesorts before they become a problem in production.

The character set utf8 in MySQL is a historical trap that allows at most three bytes per character, silently dropping or rejecting emoji and many rare scripts. utf8mb4 stores full Unicode completely but requires the right collation choice and a carefully planned migration so index lengths and sort orders do not break.

A Galera Cluster allows writes on multiple nodes at the same time and replicates synchronously instead of catching up asynchronously like classic MySQL replication. That makes failover almost invisible, but comes with certification-based replication, flow control and quorum rules that must be understood before going to production.

Without consistent table archiving, quote and sales_order accumulate millions of orphaned cart rows and years of order history that slow down every query and inflate backups. A well designed archiving strategy separates active transactional data from historical data without endangering referential integrity or compliance requirements.

MySQL and MariaDB are often treated as interchangeable because MariaDB started as a fork of MySQL. For years now, window functions, the JSON implementation, replication mechanisms and optimizer behavior have been visibly drifting apart, and anyone who makes the wrong assumptions during deployment ends up facing incompatible SQL syntax or unexpected replication behavior in production.

MySQL replication determines how many transactions can be lost in a failure and how stale the data an application reads can become. Understanding the difference between asynchronous and semi-synchronous replication, binlog processing and replication lag lets you tune a topology for actual consistency requirements instead of relying on defaults.