'Indexing' Tag

  • Purging old rows with QueryScript: three use cases

    November 14, 2012

    Problem: you need to purge old rows from a table. This may be your weekly/monthly cleanup task. The table is large, the amount of rows to be deleted is large, and doing so in one big DELETE is too heavy. You can use oak-chunk-update or pt-archiver to accomplish the task. You can also use server […]

  • How common_schema split()s tables - internals

    September 6, 2012

    This post exposes some of the internals, and the SQL behind QueryScript's split. common_schema/QueryScript 1.1 introduces the split statement, which auto-breaks a "large" query (one which operates on large tables as a whole or without keys) into smaller queries, and executes them in sequence. This makes for easier transactions, less locks held, potentially (depending on […]

  • Table split(...) for the masses

    September 5, 2012

    (pun intended) common_schema's new split statement (see release announcement) auto-splits complex queries over large tables into smaller ones: instead of issuing one huge query, split breaks one's query into smaller queries, each working on a different set of rows (a chunk). Thus, it is possible to avoid holding locks for long times, allowing for smaller […]

  • common_schema rev. 68: eval(), processlist_grantees, candidate_keys, easter_day()

    September 6, 2011

    Revision 68 of common_schema is out, and includes some interesting features: eval(): Evaluates the queries generated by a given query match_grantee(): Match an existing account based on user+host processlist_grantees: Assigning of GRANTEEs for connected processes candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use. easter_day(): Returns DATE of easter day […]

  • Announcing common_schema: common views & routines for MySQL

    July 13, 2011

    Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server. What does it do? There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes & transactions, security, internals... There are […]

  • Reasons to use AUTO_INCREMENT columns on InnoDB

    March 22, 2011

    An InnoDB table must have a primary key (one is created if you don't do it yourself). You may have a natural key at hand. Stop! Allow me to suggest an AUTO_INCREMENT may be better. Why should one add an AUTO_INCREMENT PRIMARY KEY on a table on which there's a natural key? Isn't an AUTO_INCREMENT […]

  • Multi condition UPDATE query

    January 27, 2011

    A simple question I've been asked: Is it possible to merge two UPDATE queries, each on different WHERE conditions, into a single query? For example, is it possible to merge the following two UPDATE statements into one? mysql> UPDATE film SET rental_duration=rental_duration+1 WHERE rating = 'G'; Query OK, 178 rows affected (0.01 sec) mysql> UPDATE […]

  • Simple guideline for choosing appropriate InnoDB PRIMARY KEYs

    October 21, 2010

    Risking some flames, I'd like to suggest only two options for choosing PRIMARY KEYs for InnoDB tables. I suggest they should cover 99% (throwing numbers around) of cases. PRIMARY KEY cases An integer (SMALLINT / INT / BIGINT), possibly AUTO_INCREMENT column. The combination of two columns on a many-to-many connecting table (e.g. film_actor, which connects […]

  • Thoughts and ideas for Online Schema Change

    October 7, 2010

    Here's a few thoughts on current status and further possibilities for Facebook's Online Schema Change (OSC) tool. I've had these thoughts for months now, pondering over improving oak-online-alter-table but haven't got around to implement them nor even write them down. Better late than never. The tool has some limitations. Some cannot be lifted, some could. […]

  • How often should you use OPTIMIZE TABLE? - followup

    October 4, 2010

    This post follows up on Baron's How often should you use OPTIMIZE TABLE?. I had the opportunity of doing some massive purging of data from large tables, and was interested to see the impact of the OPTIMIZE operation on table's indexes. I worked on some production data I was authorized to provide as example. The […]

Powered by Wordpress and MySQL. Theme by openark.org