Page 5 - MySQL

MySQL

AI generated

Tablespace Management and file-per-table in Practice

Maxim Mironjuk - March 22, 2025

Anyone running InnoDB without a deliberate tablespace strategy accumulates an untidy system tablespace over the years that never shrinks again. innodb_file_per_table, OPTIMIZE TABLE and general tablespaces hand back the control over disk space, backup granularity and maintenance windows that gets lost otherwise in standard operation.

MySQL

AI generated

MySQL Full-Text Search: A DIY Implementation Instead of External Search

Maxim Mironjuk - March 12, 2025

Not every search field needs Elasticsearch right away. With FULLTEXT indexes, natural language mode, and boolean mode, MySQL ships a genuine full-text search directly in the database, sufficient for many applications, without extra infrastructure, extra latency, and extra consistency problems between two systems.

MySQL

AI generated

Covering Index Strategy: Forcing Index-Only Scans on Purpose

Maxim Mironjuk - February 28, 2025

A covering index contains every column a query needs, so the server never has to touch the actual table. Anyone who applies this technique deliberately turns expensive random I/O against the table into pure, sequential index-only scans, often saving more time than any additional hardware would.

MySQL

AI generated

Partitioning Large Tables: Strategies and Limits

Maxim Mironjuk - February 21, 2025

Partitioning promises faster deletes and better maintainability for very large tables, but it is not a universal performance tool. RANGE, LIST and HASH partitioning solve different problems and bring their own restrictions around foreign keys, unique keys and query planning that need to be understood before adoption.

Magento MySQL

AI generated

Zero-Downtime Migration for Magento Stores: Database Changes Without Outages

Maxim Mironjuk - February 15, 2025

Adding a column or changing an index on a table with millions of rows in a live Magento store risks long table locks and a visible outage at checkout. A well planned zero-downtime migration combines declarative schema deployments with online schema change tools such as gh-ost and pt-online-schema-change, so customers never notice a schema change happening underneath them.

MySQL

AI generated

Backup Strategies Compared: mysqldump vs. XtraBackup

Maxim Mironjuk - February 01, 2025

mysqldump and XtraBackup solve the same task in fundamentally different ways: one exports data as SQL text, the other copies the data files directly. Which backup strategy fits your database size, allowed downtime and required restore speed decides between hours and minutes of downtime when it matters.

Magento MySQL

AI generated

Using the Percona Toolkit for Magento Stores in Practice

Maxim Mironjuk - January 25, 2025

The Percona Toolkit is a collection of command-line tools that fills exactly the gaps MySQL leaves open: schema changes without locks, systematic slow query analysis, and reliable replication consistency. Teams that use these tools deliberately against a Magento database gain control over areas that would otherwise only be manageable through risky manual intervention.

MySQL

AI generated

The MySQL Query Cache: Why It No Longer Exists

Maxim Mironjuk - January 19, 2025

The MySQL query cache promised faster reads by simply reusing results, but it foundered on a single global lock that stalled the whole instance on every write. This article explains the architecture, the concrete bottlenecks, the removal in MySQL 8.0, and how Redis and ORM based caching replace the query cache more cleanly today.

MySQL

AI generated

Gap Locks and Next-Key Locks in InnoDB Explained

Maxim Mironjuk - January 11, 2025

Gap locks are among the least understood mechanisms of InnoDB and regularly trigger surprising lock waits in practice, without any specific row seeming to be affected. This article explains what gap locks actually are, why InnoDB needs them under REPEATABLE READ to prevent phantom reads, and how next-key locks work as a combination of record lock and gap lock.

MySQL

AI generated

Detecting and Avoiding N+1 Queries: A Practical Methodology

Maxim Mironjuk - January 09, 2025

N+1 queries almost always emerge unnoticed from ORM generated code and add up under load to hundreds of extra database round trips per page view. This methodology shows how to systematically spot N+1 queries in the query log, fix them with eager loading and batch loading, and measure the actual performance gain with EXPLAIN ANALYZE.