MySQL

Most of the web runs on a handful of databases and MySQL is the one people meet first. It is a client-server relational database speaking SQL, and the thing that makes it structurally interesting is that the part storing your data is swappable. Storage engines load into and out of a running server, and the engine a table uses decides whether that table has transactions, how it locks, and what happens to it after a power cut.

That design has produced both the product’s strength and its most persistent source of confusion, because a database where two tables can behave according to entirely different rules is powerful and surprising in roughly equal measure.

Everything below assumes the modern arrangement, where the transactional engine is the default and the older one survives mainly in schemas nobody has revisited.

Storage engines are the architecture

The default engine is the general-purpose one, and it does what a database is supposed to do. Transactions with commit and rollback, crash recovery, row-level locking so two writers touching different rows do not queue behind each other, consistent reads so a long query sees a stable snapshot while others write, and foreign keys that are actually enforced.

Specialised engines exist alongside it for narrow cases, and one operates purely in memory for the temporary tables the server builds while resolving a query, with a configurable ceiling before it spills to disk.

The trap sits in the older non-transactional engine. A table using it will accept a transaction, appear to work, and silently not honour it, so a rollback does nothing and a crash leaves the table in whatever state it reached. Any schema inherited from an older project is worth checking for this, because nothing warns you.

Getting a server running locally to try any of this is easiest through a bundle, and XAMPP installs the database alongside a web server and a scripting runtime in one step.

The data dictionary, and why schema changes stopped being frightening

Metadata about tables, views, routines lives in transactional tables inside the engine rather than in loose files beside the data. That sounds like an internal detail and it changed the operational character of the database completely.

Because the dictionary is transactional, a schema change combines the dictionary update, the engine operation and the log write into one atomic operation. An alteration interrupted by a crash or a killed process does not leave a table half-modified, which used to be a genuine risk requiring manual repair.

On top of that, many alterations now complete instantly rather than rebuilding the table. Adding a column to a large table was once an operation you scheduled for a quiet night, and for a good number of cases it is now immediate.

Query features that go unused

MySQL acquired the analytical vocabulary that people assumed it lacked, and a surprising number of teams still write application code to do work the database would handle.

Window functions calculate running totals, rankings, moving averages without a self-join. Common table expressions let a query be composed of named steps, including recursive ones for walking a hierarchy, which replaces the loop somebody wrote in application code to traverse a tree.

Structured document storage is native, with a full function set for reading and modifying documents in place, and updates to part of a document are written as partial changes rather than rewriting the whole thing, including in the replication stream. Spatial support covers thousands of reference systems and calculates distances on a sphere correctly rather than treating the earth as flat.

Exploring any of it is more pleasant in a proper client than at a command prompt, and a desktop client that speaks to several database systems makes result sets and query plans considerably easier to read.

Histograms, hints and the invisible index

The MySQL optimiser gained two things that repay knowing about. Histogram statistics describe how values are actually distributed in a column, so the planner stops assuming an even spread and starts choosing sensibly on skewed data.

The other is the invisible index, and it is the most underused feature in the product. An index can be marked invisible, at which point the server continues to maintain it while the optimiser pretends it does not exist. That means you can find out whether dropping an index would hurt without dropping it, and reverse the decision in one statement rather than rebuilding it on a large table.

Anyone who has deleted an index to save space and then discovered which nightly report depended on it will appreciate what that avoids.

Users, roles and the part that confuses everybody

An account in MySQL is not a username. It is a username and a host together, so the same name connecting from two places is two separate accounts with separate privileges. That single fact accounts for a large share of the access-denied problems people encounter, and it is a deliberate design rather than an oddity.

Privileges can be bundled into roles and granted collectively, which turns permission management from a per-account exercise into something maintainable. Password history can be retained to prevent reuse, the default authentication method caches its verification to avoid a slow handshake on every connection, and the privilege tables themselves are stored transactionally so a permission change cannot be half-applied.

For day-to-day administration through a browser rather than a terminal, phpMyAdmin remains the tool most hosting environments provide and most people learn on.

Replication, and what it is not

MySQL replication copies changes from one server to others, and there are several arrangements. The straightforward asynchronous form has a primary sending its change log to replicas that apply it. A group arrangement coordinates several servers so writes survive one of them failing. A packaged replica-set configuration wraps the common setup so it can be built without assembling every piece by hand.

Global transaction identifiers are what make any of it manageable, since each transaction carries an identifier that lets a replica be repointed at a different primary without anybody working out log positions by hand. Monitoring goes through the performance schema alongside everything else.

Here is the part that needs stating in every article about this. Replication is not a backup. It faithfully copies your mistakes, so a table dropped on the primary is dropped everywhere within a second. Availability and recoverability are separate problems and need separate solutions.

Tuning, and where it will disappoint you

Default settings are deliberately conservative so the server starts on modest hardware, which means an untuned installation on a capable machine performs far below what it could. The single decision that matters most is the size of the buffer pool, the memory the engine uses to cache data and indexes, since a pool large enough to hold the working set turns most reads into memory access.

Configuration can now be changed and persisted through a SQL statement rather than by editing a file and restarting, and resource groups allow threads to be assigned so that one heavy workload cannot starve everything else.

The honest limitation is parallelism within a single query. Work is largely handled by one thread per query, so MySQL scales beautifully across many concurrent connections and poorly across one enormous analytical query. A machine with thirty-two cores will not throw all of them at a single aggregation over a hundred million rows. For that kind of work PostgreSQL parallelises within a query and is the better tool, which is why plenty of organisations run both.

Conclusion

MySQL deserves its position. The transactional engine underneath is solid, the data dictionary made schema changes safe rather than nerve-wracking, replication arrangements cover most availability requirements, and the query vocabulary is far richer than its reputation among people who have not looked at it recently.

Approach it knowing three things. The defaults are set for a small machine and the buffer pool is the setting that matters. An inherited schema may contain tables that quietly ignore transactions. And one huge query will use one core, so analytical workloads belong elsewhere while transactional ones belong here.

Those three understood, it will run a busy application for years without complaint, which is precisely why so much of the web sits on top of it.