Tag: MySQL

1. Tablespace Management and file-per-table in Practice

   Mar 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.

   Read more

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

   Mar 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.

   Read more

3. Covering Index Strategy: Forcing Index-Only Scans on Purpose

   Feb 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.

   Read more

4. Partitioning Large Tables: Strategies and Limits

   Feb 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.

   Read more

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

   Feb 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.

   Read more

6. Backup Strategies Compared: mysqldump vs. XtraBackup

   Feb 1, 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.

   Read more

7. Using the Percona Toolkit for Magento Stores in Practice

   Jan 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.

   Read more

8. The MySQL Query Cache: Why It No Longer Exists

   Jan 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.

   Read more

9. Gap Locks and Next-Key Locks in InnoDB Explained

   Jan 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.

   Read more

10. Detecting and Avoiding N+1 Queries: A Practical Methodology

    Jan 9, 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.

    Read more