A place for code I want to explain, mistakes worth remembering, and the occasional story from my life.
MySQL 2 min read
A unique external-reference column prevents duplicate populated references, but several rows with NULL can still exist. In MySQL, a unique index permits multiple NULL values. That...
MySQL 2 min read
Deleting a customer can fail because invoices reference it, or can remove related rows if cascading deletion is configured. That difference is a data-retention decision, not just a...
MySQL 2 min read
A product search for AX% and a search for %AX% look similar to a user, but they ask the database to do different work. A prefix search can align with an ordinary ordered index in s...
MySQL 2 min read
A list shows only an order ID and creation time for one tenant. If the chosen index contains every value needed by the query, MySQL may be able to answer from the index without fet...
MySQL 2 min read
A slow query is reported and someone proposes an index on every filtered column. More indexes increase storage and write work, and separate indexes do not necessarily support the q...
MySQL 2 min read
A report needs customers whose outstanding invoice total exceeds a threshold. WHERE filters individual rows before grouping; HAVING filters grouped results. Putting the aggregate c...
MySQL 2 min read
A daily report uses an end time of 23:59:59 and misses rows with fractional seconds after that instant. A half-open range includes the start and excludes the next day's start, avoi...
MySQL 2 min read
A cleanup query looks for products whose IDs are not in an exclusion table. One NULL in the exclusion result can make the NOT IN condition evaluate unexpectedly, leaving the cleanu...
MySQL 2 min read
A list needs customers who have at least one overdue invoice. Joining every overdue invoice creates duplicate customer rows, then DISTINCT is added to hide them. An EXISTS conditio...