Mysql

@row := @row + 1. I have a report in production that stands on that trick, and the trick was never guaranteed to work. Evaluation order of user variables in SELECT is undefined, it just happened to behave. MySQL 8.0 went GA last week, and for me the release is about SQL. Window functions and CTEs, the things Postgres people stopped noticing years ago, are here. The classic task: top three orders per customer. In 5.7 you had a self-join nobody could read a month later, or the variable trick. Now: ...

Forty million rows, three application instances behind a balancer, one migration that renames a column. The deploy script runs migrations first, then rolls the code instance by instance. For a few minutes old code was writing to a column that no longer existed. The error rate graph looked like a wall. I had approved that migration. It was one line. Migration tools trained us to think a schema change is a code change. Write a file, run it, done. A schema change on a big table takes time, takes locks, and while it runs the previous version of the application is still serving traffic. Both versions must survive both schemas. That is the whole discipline, and it has a name: expand and contract. ...

Fifty rows. Three indexes on the table, one per column. Still slow. The query is the standard one from any multi-tenant application: SELECT * FROM orders WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT 50; “Add an index” is the typical reaction, and one index on each column is the typical result. The number of indexes is not the point. The order of columns inside one index is. ...

ALTER TABLE users ADD profile JSON; MySQL 5.7 went GA this week, and this line is now legal. Native JSON type: validation on insert, binary storage, functions to read paths. The question is not whether it works. It is what belongs in it. Classic case: user profile with flexible metadata. Marketing wants a new field every other week. The old options were bad in familiar ways. Forty nullable columns and an ALTER for every idea. Or EAV, key-value rows, where every read is a self-join festival and nothing has a type. ...