MySQL UC2008 presentation "Grand Tour of the information schema" now online

Yup, the presentation slides as well as the scripts are now available online on the conference website.

The stuff you will find in there:

Information_schema.pdf

Diagram of the information schema

I_S_VC_Slides.pdf

Slides for the UC presentation, "Grand Tour of the Information Schema"

I_S_INDEXES2.sql

script, returns one row for each index (rollup of information_schema.STATISTICS)

I_S_REDUNDANT_INDEXES2.sql

script, lists all redundant indexes. Redundancy rules:

• two indexes with the same columns, type and uniqueness are interchangeable. The one with the largest index name is listed as redundant


• if there is a unique index and a non unique index with the same columns and type exists, the non-unique one is considered redundant


• A non-unique btree index is the leftmost prefix of another btree index (that may or may not be unique)

TODO: indexes created on the referencing columns for InnoDB foreign keys are often redundant. It would be nice if the script could list that the FK currently supports an FK

p_check_fk_constraint_violations.sql

stored procedure Detect probles after using @@foreign_key_checks=1. Takes a schema name LIKE pattern and a table name LIKE pattern and reports any foreign keys constraints for which violations exist. TODO: if the referenced table in the foreign key was dropped, the script will encounter a runtime error. Would be nice to properly report instead

qwz.sql

Stored procedure, The Query wizard for the mysql command line pronounced "cues". To use it, run the script, and it starts a new session with the query wizard. Alternatively, do: USE qwz; CALL qwz(NULL); and follow the on-screen instructions.

schema_doc.sql

script, Generates HTML documentation for the current schema. Run using a command like: mysql -uroot -Dsakila -Nrs -e"source schema_doc.sql" > schema_doc.html

schema_doc.sh

shell, Linux example for running schema_doc.sql

gen_fks.sql

script, Generates trigger code to emulate foreign keys. Set a schema containing foreign key constraints as the default database. Run the script. The output forms trigger code that may be used to recreate the foreign keys functionality in a database that has the same tables but no declarative foreign keys. For foreign keys with the RESTRICT or NO ACTION rules, a non-existent stored procedure will be called: p_child_row_exists(). For orphan INSERTs and UPDATEs, a call is made to p_no_parent_row(). Currently this is the best we can do - it will result in a runtime error and the action causing it will not complete but it is not ideal of course.Corrective rules (CASCADE etc) should just work.

p_create_federated_table.sql

stored procedure, Creates a federated table (residing on a 5.0 or better remote host)