'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 [...]

  posted in MySQL by shlomi

• 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 [...]

  posted in MySQL by shlomi

• 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 [...]

  posted in MySQL by shlomi

• 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 [...]

  posted in MySQL by shlomi

• 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 [...]

  posted in MySQL by shlomi

• 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 [...]

  posted in MySQL by shlomi

• 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 [...]

  posted in MySQL by shlomi

• 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 [...]

  posted in MySQL by shlomi

• 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. [...]

  posted in Development, MySQL by shlomi

• 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 [...]

  posted in MySQL by shlomi