Tag: MySQL

1. Generated Columns for Faster Queries on Computed Values

   Sep 16, 2025

   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.

   Read more

2. Row-Level Locking vs. Table Locking in MySQL

   Sep 9, 2025

   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.

   Read more

3. InnoDB Buffer Pool: Understanding Architecture and Sizing

   Sep 6, 2025

   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.

   Read more

4. Systematically Cleaning Up Magento Log and Report Tables

   Sep 1, 2025

   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.

   Read more

5. EXPLAIN ANALYZE: Learning to Really Read Query Plans

   Aug 10, 2025

   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.

   Read more

6. Charset and Collation: Using utf8mb4 Correctly

   Aug 2, 2025

   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.

   Read more

7. Galera Cluster: Multi-Master Setup and Its Pitfalls

   Jul 18, 2025

   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.

   Read more

8. Quote and Sales Table Archiving for Large Magento Stores

   Jul 11, 2025

   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.

   Read more

9. MySQL 8 vs. MariaDB: Differences That Matter in Production

   Jul 11, 2025

   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.

   Read more

10. MySQL Replication: Async vs. Semi-Sync Explained

    Jun 23, 2025

    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.

    Read more