browse/

15 results
databases/sql-window-functions-how-every-row-gets-a-rank-or-running-total-withou.md SQL window functions: how every row gets a rank or running total without collapsing the table A window function adds a calculated column to every row without collapsing rows the way GROUP BY does. Learn what OVER, PARTITION BY and… 4 min read · 8 Nov databases/pagination-why-next-page-shows-a-post-twice-and-how-a-bookmark-fixes-i.md Pagination: why 'next page' shows a post twice, and how a bookmark fixes it Why does "next page" sometimes show the same post twice, and why does page 90,000 load slowly? Both come from paging with LIMIT and OFFSET… 5 min read · 1 Nov databases/caching-why-the-second-read-is-fast-and-how-a-copy-goes-stale.md Caching: why the second read is fast, and how a copy goes stale Why does a page load slowly the first time and fast the second? Usually a cache: a quick copy of an answer the database already gave. Run a… 5 min read · 29 Oct databases/the-n-1-query-problem-why-a-page-that-asks-the-database-501-questions.md The N+1 query problem: why a page that asks the database 501 questions is slow A page that lists authors and their books can quietly send one query per author. With 500 authors that is 501 trips to the database, and… 4 min read · 26 Oct databases/transactions-why-a-money-transfer-must-save-both-changes-or-neither.md Transactions: why a money transfer must save both changes or neither Sending £50 between two accounts takes two changes to a database. If the power fails between them, the money vanishes. A transaction groups… 5 min read · 23 Oct security/sql-injection-how-a-quote-mark-in-a-login-form-can-rewrite-the-databas.md SQL injection: how a quote mark in a login form can rewrite the database's question A login form builds a database question out of the words you type. If it glues your text straight into the question, your text can rewrite… 5 min read · 21 Oct databases/sql-indexes-why-finding-one-row-among-200-000-takes-about-18-steps.md SQL indexes: why finding one row among 200,000 takes about 18 steps A database with 200,000 customers can answer "find this email" by reading every row, or by reading about 18 entries. The difference is an… 4 min read · 20 Oct databases/sql-group-by-how-many-rows-become-one-row-per-customer.md SQL GROUP BY: how many rows become one row per customer A shop's orders table has 8 rows, but the owner wants one line per customer. GROUP BY sorts rows into piles by a column's value, then an… 4 min read · 17 Oct databases/sql-null-why-two-people-vanish-from-both-your-queries.md SQL NULL: why two people vanish from both your queries Two people have no age in your table. You ask for everyone older than 30, then for everyone not older than 30, and the two lists add up to… 4 min read · 14 Oct databases/sql-joins-how-two-tables-become-one-answer-inner-left-right-full.md SQL JOINs: how two tables become one answer (INNER, LEFT, RIGHT, FULL) Your data lives in two tables, but your question needs both. A JOIN lines them up, and the four kinds (INNER, LEFT, RIGHT, FULL) differ… 5 min read · 11 Oct databases/database-transactions-why-moving-money-is-all-or-nothing.md Database transactions: why moving money is all or nothing Paying someone is really two changes in a database: take money from one account, add it to another. If the power dies between them, money… 6 min read · 7 Oct security/sql-injection-how-a-stray-quote-mark-turns-data-into-instructions.md SQL injection: how a stray quote mark turns data into instructions A search box looks harmless, but if a program builds its database question by gluing text together, a stray quote mark can turn a visitor's… 4 min read · 6 Oct