Page 4 - MySQL

Read replicas promise more read capacity without bigger hardware, but blindly distributing reads across replicas earns you stale data and hard-to-reproduce bugs. Only a deliberate read/write splitting strategy with a clear consistency approach makes MySQL read replicas a reliable building block for scaling.

Poorly sized connection pooling leads either to too many connections errors under load or to wasted resources from thousands of unused idle connections. This article shows how to correctly size max_connections, how wait_timeout and interactive_timeout work together, how to align PHP-FPM pool size with the MySQL connection limit, and when an external connection pooler such as ProxySQL makes sense.

A MySQL failover that loses data or blocks the application for minutes when it matters is not a safety net, it is an added risk. Distinguishing planned from unplanned failover, consistently using GTID, and testing failover regularly turns an abstract emergency plan into a reliable operational process.

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.

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.

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.

The four isolation levels in MySQL sound similar in the documentation, yet they behave fundamentally differently under load. This article explains READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE concretely in InnoDB, with a particular focus on the default isolation level REPEATABLE READ and its snapshot-based consistent-read behaviour.

The native JSON type lets you store semi-structured data directly in MySQL and query it efficiently with JSON_EXTRACT and JSON_TABLE. Without generated columns for indexing, however, JSON queries stay slow, and without clear criteria for its use, JSON quickly turns into an excuse for missing schema design.