Mysql Information Schema

Re-publishing from my wiki.

• Show tables that use Barracuda disk format

select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema') AND ( ROW_FORMAT='Compressed' OR ROW_FORMAT='Dynamic'); 

• Show me all tables that are InnoDB 

SELECT `table_schema`, `table_name` FROM `information_schema`.`TABLES` WHERE `Engine`='Innodb' AND `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql'; 

• Show me all tables that are MyISAM 

SELECT `table_schema`, `table_name` FROM `information_schema`.`TABLES` WHERE `Engine`='MyISAM' AND `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql'; 

• Print Queries to aid in conversion FROM MyISAM to InnoDB 

 use `information_schema`; SELECT CONCAT("ALTER TABLE `" , `TABLE_SCHEMA`, "`.`", `table_name`, "` Engine=Innodb;") AS "" FROM `information_schema`.`TABLES` WHERE `Engine`='MyISAM' AND `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql'; 

You can save the above in a file and run this
mysql --batch < input.sql > out.sql 

• Show me a count of tables grouped by engine type 

SELECT `Engine`, count(*) as Total FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql' GROUP BY `Engine`;

• Show me the datasize and index size of all tables grouped by engine type

SELECT `Engine`, COUNT(ENGINE), sum(data_length)/(1024*1024*1024) as 'Datasize-GB', sum(index_length)/(1024*1024*1024) as 'Indexsize-GB' FROM `information_schema`.`TABLES` GROUP BY `Engine`; 

• Show me the top 10 tables by size outside of information_schema and mysql 

SELECT TABLE_SCHEMA, TABLE_NAME,data_length/1024*1024 FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql' ORDER BY `data_length` DESC LIMIT 10; 

• Tables without indexes

USE `information_schema`; SELECT CONCAT(TABLES.table_schema,".",TABLES.table_name) as name, `TABLES`.`TABLE_TYPE`,`TABLE_ROWS` FROM `TABLES` LEFT JOIN `TABLE_CONSTRAINTS` ON `TABLES`.`table_schema` = `TABLE_CONSTRAINTS`.`table_schema` AND `TABLES`.`table_name` = `TABLE_CONSTRAINTS`.`table_name` AND `TABLE_CONSTRAINTS`.`constraint_type` = 'PRIMARY KEY' WHERE `TABLE_CONSTRAINTS`.`constraint_name` IS NULL; Check for redundant indexes SELECT * FROM ( SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME`, GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX`) AS columns FROM `information_schema`.`STATISTICS` WHERE `TABLE_SCHEMA` NOT IN ('mysql', 'INFORMATION_SCHEMA') AND NON_UNIQUE = 1 AND INDEX_TYPE='BTREE' GROUP BY `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME` ) AS i1 INNER JOIN ( SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME`, GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX`) AS columns FROM `information_schema`.`STATISTICS` WHERE INDEX_TYPE='BTREE' GROUP BY `TABLE_SCHEMA`, `TABLE_NAME`, `INDEX_NAME` ) AS i2 USING (`TABLE_SCHEMA`, `TABLE_NAME`) WHERE i1.columns != i2.columns AND LOCATE(CONCAT(i1.columns, ','), i2.columns) = 1 

• List character sets 

 SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `CHARACTER_SET_NAME`, `TABLE_COLLATION` FROM `INFORMATION_SCHEMA`.`TABLES` INNER JOIN `INFORMATION_SCHEMA`.`COLLATION_CHARACTER_SET_APPLICABILITY` ON (`TABLES`.`TABLE_COLLATION` = `COLLATION_CHARACTER_SET_APPLICABILITY`.`COLLATION_NAME`) WHERE `TABLES`.`TABLE_SCHEMA` !='information_schema' AND `TABLES`.`TABLE_SCHEMA` !='mysql' ;

• List average row length and index length 

SELECT CONCAT (`TABLE_SCHEMA`, "." , `TABLE_NAME`) as name , `AVG_ROW_LENGTH`, `DATA_LENGTH`, `INDEX_LENGTH` FROM `TABLES` ORDER BY `AVG_ROW_LENGTH` DESC LIMIT 15; 

• Oldest tables with respect to update times 

SELECT CONCAT (`TABLE_SCHEMA`, "." , `TABLE_NAME`) as name , `UPDATE_TIME` FROM `TABLES` WHERE `UPDATE_TIME` IS NOT NULL ORDER BY `UPDATE_TIME` LIMIT 10; 

• Tables with foreign keys 

SELECT * FROM `table_constraints` WHERE `constraint_type` = 'FOREIGN KEY' ; 

• List of indexes and their total count 

SELECT `INDEX_TYPE`, count(*) as NUM FROM `STATISTICS` group by `INFORMATION_SCHEMA`.`INDEX_TYPE`; 

• Get a summary of privileges 

SELECT * from `INFORMATION_SCHEMA`.`USER_PRIVILEGES`;