Türker Yıldırım

mysql

  • A BuddyPress activity item is not a post, and the query is different

    Note2 min read

    The activity stream lives in its own tables with its own query class, so every WordPress instinct about WP_Query, post meta and the loop is wrong here. The…

  • Restoring to a scratch database on a schedule, automatically

    Note2 min read

    The restore is the part that fails, and finding that out during an incident is the worst possible moment to discover the dump has been missing a table…

  • The leftmost prefix rule, in one example

    Note1 min read

    A composite index on (a, b, c) serves queries filtering on a, on a and b, and on all three — and does nothing for a query filtering…

  • firstOrCreate is not atomic, and what to do instead

    Note2 min read

    It reads, finds nothing, and inserts. Two requests doing that concurrently both find nothing and both insert, which is why the duplicates appear only under load. The unique…

  • Roles instead of one user per application

    Note2 min read

    Six applications with six database users means six sets of grants maintained by hand, and the reporting tool ends up with write access because copying an existing user…

  • SKIP LOCKED is why a queue table is suddenly viable

    Note2 min read

    Two workers selecting the next unprocessed row with FOR UPDATE serialise — the second waits for the first — so adding workers adds no throughput at all. SKIP…

  • Testing what the database actually does

    Deep dive10 min read

    The repository test passed against a mock and failed against MySQL. A mock asserts what you believe the database does, which is the thing in question.

  • wp-cli db query beats phpMyAdmin for one-off work

    Note1 min read

    Reaching for a GUI to answer one question about a database means finding credentials, a tunnel and a browser, when the site’s own configuration already has all of…

  • Seven services in one development environment

    Deep dive10 min read

    Apache, Nginx, PHP, MySQL, Redis, Beanstalkd and a mail catcher, on a laptop. Onboarding measured in minutes rather than days, and what it costs to run.

  • LAG and LEAD replace a self join on the same table

    Note2 min read

    Comparing a row with the previous one — time between orders, change since the last reading — was a self join on a correlated subquery finding the preceding…