Page 8 - MySQL

MySQL 8 introduced two small but practically important index features that were simply missing in earlier versions: true descending indexes and invisible indexes. Before, the optimizer simulated a descending sort with a backward scan over an ascending index, which hit a hard wall once mixed sort directions across several columns were involved. And an existing index could only be tested by actually dropping it, a risky step in a production Magento database. This article covers how both features work internally, when rebuilding an existing index is genuinely worth it, and how to make index changes to a live installation with minimal risk.

Every call to clock_gettime, every timestamp generated by MySQL, and every latency measurement in PHP-FPM depends on the Linux kernel's active clocksource. The Time Stamp Counter (TSC) is orders of magnitude faster than HPET on modern hardware, but was long known for synchronization problems across CPU cores that still produce false expectations and misconfigurations today.

A trigger that handles an extra check or logging step on every INSERT looks harmless and convenient at first glance. Only under load does it become clear that triggers run synchronously inside the same transaction context, extending lock times, raising deadlock risk, and producing errors that are hard to trace back. On frequently updated tables in a Magento shop, such as stock or orders, the choice between trigger, event scheduler, and application logic directly decides stability under load.

Prepared statements are often understood purely as a defense against SQL injection, but they are also a performance tool for repeatedly executed queries. This article explains why string concatenation is structurally unsafe, how prepared statements solve this problem, what the difference between server-side and client-side prepare is, and shows correct implementations with PDO and mysqli, including common pitfalls.

A migration runner ensures database schemas change traceably and in the right order across multiple environments. This article shows how to build a custom migration runner in Bash, with a tracking table, checksum verification, transactional execution, and a slim command line interface for up, down, and status.

An index on a column with only two possible values almost never delivers a noticeable speedup, no matter how large the table. Cardinality describes the number of distinct values in a column, selectivity its ratio to the row count, and together with the optimizer both figures decide whether an index gets used at all.

The slow query log is only the first step, because a raw file with thousands of lines does not deliver insight on its own. This article shows how to choose long_query_time sensibly, how pt-query-digest and mysqldumpslow aggregate the data, and how to turn that into a solid prioritization for the next round of optimizations.

Talking about temporary tables in MySQL often means two entirely different things: tables explicitly created with CREATE TEMPORARY TABLE and bound to a session, and internal optimizer constructs that MySQL itself creates for GROUP BY, ORDER BY, or DISTINCT without anyone asking for them. Then there is the MEMORY storage engine for explicitly created, permanently in-memory lookup tables. Anyone who keeps these three concepts cleanly separated also understands why some queries turn unexpectedly slow and how to fix that in a targeted way.

Optimizer hints such as USE INDEX, FORCE INDEX, and STRAIGHT_JOIN intervene directly in the query optimizer's decisions and can solve real problems with stale statistics, but they can also create new ones when applied carelessly. This article shows the syntax, the effect, and the clear line between legitimate optimization and risky code smell.

Anyone who treats transactions in MySQL as mere syntax without knowing what InnoDB does behind the scenes risks inconsistent data under load. This article shows how START TRANSACTION, COMMIT and ROLLBACK actually behave, how autocommit affects everyday work, and how InnoDB technically guarantees each single ACID property, from the undo log to the redo log.