VIEWS on INFORMATION_SCHEMA tables - Useful stuff

Views in MySQL really has quite a bad reputation, for bad performance mainly, but also there were some stability issues at some point. Now they are pretty stable, but I don't see them used that much. One place where I like to use them myself is in combination with INFORMATION_SCHEMA tables. The I_S tables are really useful and contain a lot of information, and by using VIEWs we can massage the data a bit.

I often have a database specifcally for DBA needs, so that is what we will use here.
CREATE DATABASE IF NOT EXISTS dba;
USE dba;

OK, now we have a database to play with. Lets solve a minor problem first. Being able to use the TABLES table in INFORMATION_SCHEMA is great, as it allows standard SQL filtering and processing, in difference to the output from SHOW TABLES (largely at least, some filtering is available in the SHOW commands also of course). But the TABLES table contains the tables in ALL databases in the MySQL instance, not just the current database. Also, although the INFORMATION_SCHEMA tables are neat, typing INFORMATION_SCHEMA to access these tables all the time is tiresome. This is something that is easily fixed with a view. In the dba database, create a VIEW like this:
CREATE OR REPLACE VIEW dba.tables AS
SELECT * FROM information_schema.tables
WHERE table_schema = database();

Note that this is "dynamic", i.e. the database() function is evaluated when a query runs against the view, not when the view is created. So if we change to the test database and issues the command:
USE test;
SELECT * FROM dba.tables;

You will get the tables in the test database. Pretty neat and useful. Another issue with the I_S tables is that there is a whole lot of data in them, the data is often at a low level and sometimes they are not organized in a way to make using them easy. Case in point is the GLOBAL_STATUS table. This has a LOT of values, 300+ in the version of MySQL I use, and often you are only interested in some values, which is solved by filtering. But some values are missing, like cache hit ratios. Which is not to say that cache hit ratios cannot be computed from values in the GLOBAL_STATUS table. But we can make things easier with a view:
CREATE OR REPLACE VIEW dba.global_status(
variable_name, variable_value)
AS SELECT gs1.variable_name, gs1.variable_value
FROM information_schema.global_status AS gs1
UNION SELECT 'INNODB_CACHE_HIT_RATIO', ROUND((1 - (gs1.variable_value
/ gs2.variable_value)) * 100, 0)
FROM information_schema.global_status AS gs1
STRAIGHT_JOIN information_schema.global_status AS gs2
WHERE gs1.variable_name = 'INNODB_BUFFER_POOL_READS'
AND gs2.variable_name = 'INNODB_BUFFER_POOL_READ_REQUESTS'
UNION SELECT 'MYISAM_CACHE_HIT_RATIO', ROUND((1 - (gs1.variable_value
/ gs2.variable_value)) * 100, 0)
FROM information_schema.global_status AS gs1
STRAIGHT_JOIN information_schema.global_status AS gs2
WHERE gs1.variable_name = 'KEY_READS'
AND gs2.variable_name = 'KEY_READ_REQUESTS'
UNION SELECT 'QCACHE_CACHE_HIT_RATIO', ROUND((gs1.variable_value
/ gs2.variable_value) * 100, 0)
FROM information_schema.global_status AS gs1
STRAIGHT_JOIN information_schema.global_status AS gs2
WHERE gs1.variable_name = 'QCACHE_HITS'
AND gs2.variable_name = 'COM_SELECT'
ORDER BY variable_name;

Now, we have cache hit ratio values added to the global status. All the existing status values are still there. Also, note an interesting MySQL extension to VIEWs here: You can add an ORDER BY to them!

/Karlsson