MySQL-Schema-Queries

From DevRandom

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

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 do 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`;