The other day, I was troubleshooting a deadlock, and I wondered if any of the table’s columns were referenced by any foreign keys (fks) from any other tables in the instance.

Well, this is actually very simple with information_schema (I_S):

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME='parent';

Where `parent` is the name of the table you’re searching for.

Note this query does not restrict on the database, or schema, name, but that could easily be added (or any other number of conditions). Here is an example where I only return the most useful columns (which could be useful for determining said conditions):

SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA,
	TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA,
	REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE

Tracking down InnoDB lock information using the new Information Schema tables provided with the InnoDB plugin has never been easier.

Long story short, the other day I was trying to identify what transaction was holding the lock for a particular UPDATE. The UPDATE would not complete and kept timing out with “Lock wait timeout exceeded; try restarting transaction”.

Of course I checked the output of SHOW ENGINE INNODB STATUS. From that output, I could tell it was the replication thread holding the lock, *since* it was the only other transaction running. But I could not verify this with output, it just had to be the case.

Next I enabled the InnoDB lock monitor, and examined that output too. Here, it showed more detail on the locks being held, however, it only shows the first 10 locks held by the

Lately, I've had a few people ask me "How much data is in our database?". Sure, you can look at the file sizes of MyISAM tables and indexes and get a ballpark figure, but what if you need exact results, or are running InnoDB storage engine? That proves to be more challenging! In playing around with the information_schema, I've put together some queries to help:

Calculate Index Sizes

mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES 
WHERE table_schema LIKE 'database'; 

+------------------+
|Total Index Size  |
+------------------+
|1.70 GB           |
+------------------+

1 row in set (1.60 sec) 

Calculate the Total Size of Stored Data

mysql> SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS

Lately, I've had a few people ask me "How much data is in our database?". Sure, you can look at the file sizes of MyISAM tables and indexes and get a ballpark figure, but what if you need exact results, or are running InnoDB storage engine? That proves to be more challenging! In playing around with the information_schema, I've put together some queries to help:

Calculate Index Sizes

mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES 
WHERE table_schema LIKE 'database'; 

+------------------+
|Total Index Size  |
+------------------+
|1.70 GB           |
+------------------+

1 row in set (1.60 sec) 

Calculate the Total Size of Stored Data

mysql> SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS

I was asked this question recently “I am wanting to create a simple MySQL database consisting of 5 tables”?

While it’s easy to tell people to RTFM, the question does warrant an answer for the MySQL beginner to provide a more specific guidance as to where to start, and what to do. As a expert in MySQL it’s easy to forget how you would describe what to do. Here are my tips to getting started.

Step 1. Download the MySQL 5.1 software for your platform (e.g. Linux, Windows, Mac etc) from MySQL 5.1 Downloads. There are many different versions of MySQL, MySQL 5.1 is the current production version.

Step 2. You will need to install the MySQL software. The MySQL reference manual is the place to go, Chapter 2

The information schema in MySQL is a great tool for obtaining just about any type of meta-data about your MySQL server. It is a schema consisting of views that provide just about any information you would want to know about your MySQL installation. For instance, you can find out status and system variables, process listing, database object listing - schemas, tables, triggers, stored procedures and their organization within MySQL, user privileges. .

Originally, people would use SHOW commands to obtain this information. For instance, to see all the schemas (databases) within your MySQL instance:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| admin              |
| aps                |
| contacts_db        |
| fed                |
| memc               |
| metatalk           |
| myschema           |
| mysql              |
| sakila