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');
