Sql

@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: ...

LIMIT 50 OFFSET 500000. Page 10001 of an events table, from a paginator someone wrote in an afternoon. Postgres has no shortcut to row 500000. It walks the index through half a million entries, fetches them, throws them away, and returns fifty. Page one is fast. Page ten thousand is slow, and every page after it is slower. Run EXPLAIN ANALYZE on both: the plans are identical, the numbers are not. ...

38 seconds to 11. Same query, same forty million rows, no index added, no SQL changed. The only difference is PostgreSQL 9.6, released yesterday, and one setting. The query is a typical report: count and sum over an events table, grouped by day, three months of data. On 9.5 the plan is one process grinding through the table. On 9.6 with parallelism enabled: Finalize HashAggregate -> Gather Workers Planned: 4 -> Partial HashAggregate -> Parallel Seq Scan on events Four workers scan their own chunks, aggregate partially, the leader merges. Just more hands. For years we optimized SQL as if the database had exactly one worker per query. That assumption expired yesterday. ...

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

$row = $db->fetchOne('SELECT id FROM counters WHERE name = ?', array($name)); if ($row) { $db->execute('UPDATE counters SET value = value + 1 WHERE name = ?', array($name)); } else { $db->execute('INSERT INTO counters (name, value) VALUES (?, 1)', array($name)); } Works on the laptop. In production two requests arrive in the same millisecond. Both SELECT, both see nothing, both INSERT. One dies with a duplicate key error. Or worse, there is no unique constraint, and now you have two rows and a bug report you cannot reproduce. The window between SELECT and INSERT is tiny, so it fires once a week, always for someone else. ...

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

Fifty one queries. That is what the SQL panel of the Kohana profiler showed for one catalog page. Fifty posts on the page. Nothing changed in the code. The page took two seconds because there was more data than in spring, and the code was written for spring. $post->author->name in a loop. One query for the list of posts, then one more for the author of every post, inside the foreach. The line looks innocent, and that is the problem: lazy loading hides the price. You write the relation, you go home early, and the bill comes in six months. ...