Türker Yıldırım

mysql

  • ROWS and RANGE differ exactly when there are ties

    Note2 min read

    ROWS counts physical rows and RANGE counts values, so they are identical until two rows share an ordering value. RANGE is the default when a frame is not…

  • JSON_TABLE turns a document into rows

    Note2 min read

    A JSON array in a column can be joined against as a table, which is occasionally the right answer and is more often a sign the data wanted…

  • wp_options autoload, ranked by size

    Note2 min read

    The total autoloaded payload is a single number that predicts a large fraction of baseline request time, and almost nobody knows theirs. Tracking the figure over time is…

  • Window functions with an explicit frame clause

    Note2 min read

    Adding ORDER BY to a window changes what the aggregate means, because it changes the default frame from the whole partition to everything up to the current row.…

  • The report that should have been a materialised view

    Deep dive10 min read

    MySQL has no materialised views and the report needs one anyway. A summary table, a watermark, and the backdated edit that breaks both.

  • An autoloaded option that holds a cache

    Note2 min read

    Every autoloaded option is fetched on every request, and a plugin storing a cache in one turns a cache into a fixed cost. The total autoloaded size is…

  • A migration that ran for four hours on a table nobody could lock

    Deep dive14 min read

    An ALTER on forty million rows and a maintenance window nobody would approve. Online DDL is online until it is not.

  • A custom table when postmeta is the wrong shape

    Note1 min read

    Post meta is a key-value store with an index on the key, and querying it by value is a join per condition with no useful index. The string…

  • The leftmost prefix rule, and three indexes that are one

    Note2 min read

    A composite index serves any query using a leftmost prefix of its columns, which means three separate indexes are frequently one index somebody did not notice. The gap…

  • A covering index, and the table it never touches

    Note2 min read

    An index containing every column a query needs answers it without reading the table, which on a wide row is the difference between one page read and two.…