'INFORMATION_SCHEMA' Tag

• Hierarchical data in INFORMATION_SCHEMA and derivatives

  January 8, 2013

  Just how often do you encounter hierarchical data? Consider a table with some parent-child relation, like the this classic employee table: CREATE TABLE employee (   employee_id INT UNSIGNED PRIMARY KEY,   employee_name VARCHAR(100),   manager_id INT UNSIGNED,   CONSTRAINT `employee_manager_fk` FOREIGN KEY (manager_id) REFERENCES employee (employee_id) ) engine=innodb ; +-------------+---------------+------------+ | employee_id | employee_name [...]

  posted in MySQL by shlomi

• Killing InnoDB idle transactions

  December 4, 2012

  The issue of terminating long-time idle open InnoDB transaction has been discussed recently by many. I wish to add my share, by proposing a quick and clean solution via common_schema. common_schema 1.2 provides with the innodb_transactions view, which relies on INNODB_TRX - one of the InnoDB Plugin views in INFORMATION_SCHEMA - as well as on [...]

  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

• Auto caching INFORMATION_SCHEMA tables: seeking input

  March 8, 2012

  The short version I have it all working. It's kind of magic. But there are issues, and I'm not sure it should even exist, and am looking for input. The long version In Auto caching tables I presented with a hack which allows getting cached or fresh results via a simple SELECT queries. The drive [...]

  posted in MySQL by shlomi

• INFORMATION_SCHEMA Optimizations: still crashing my servers

  December 12, 2011

  [Update: need to take more breaks: now NOT crashing my servers! See clarifications below] INFORMATION_SCHEMA Optimizations are meant to make your INFORMATION_SCHEMA queries lighter and safer. For example, if you're going to query the COLUMNS table for just the columns of a single table, then the following: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='sakila' AND TABLE_NAME='rental' [...]

  posted in MySQL by shlomi

• More MySQL foreach()

  December 2, 2011

  In my previous post I've shown several generic use cases for foreach(), a new scripting functionality introduced in common_schema. In this part I present DBA's handy syntax for schema and table operations and maintenance. Confession: while I love INFORMATION_SCHEMA's power, I just hate writing queries against it. It's just so much typing! Just getting the [...]

  posted in Development, MySQL by shlomi

• Reading results of SHOW statements, on server side

  November 25, 2011

  SHOW statements are show stoppers on server side. While clients can get a SHOW statement as a result set just as any normal SELECT, things are not as such on server side. On server side, that is, from within MySQL itself, one cannot: SELECT `Database` FROM (SHOW DATABASES); One cannot: DECLARE show_cursor CURSOR FOR SHOW [...]

  posted in MySQL by shlomi

• MySQL Global status difference using single query

  August 12, 2011

  Have just read MySQL Global status difference using MySQL procedures / functions, by Andres Karlsson. Have commented, but realized I did not provide with a direct answer. In the comment, I suggested checking out a solution based on views, found in common_schema. But the solution in common_schema is split into two views, due to the [...]

  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