Saturday, October 24, 2009

Accessing Metadata through Stored Routines

Accessing metadata can be optimized by using stored routines.  Stored routines provide the ability to filter the data in a more useful way.  For example, when I'm looking at table data I usually want to look at the index information also.  So I use a stored routine called tabinfo that gives me key information I need for tables and indexes.

-- Create the tabinfo stored procedure.
DROP PROCEDURE IF EXISTS  tabinfo;

CREATE PROCEDURE tabinfo(ptableschema  VARCHAR(30))
SELECT  t.table_name, engine, table_rows,
                i.column_name, i.index_name, i.cardinality
FROM     information_schema.tables t, information_schema.statistics i
WHERE  t.table_name = i.table_name
      AND  t.table_schema = i.table_schema
      AND  t.table_schema = ptableschema
ORDER BY t.table_rows DESC;


-- Execute the tabinfo stored routine using the CALL command.
mysql> CALL tabinfo('world');