|
Saturday, 22 October 2011 01:44 |
// Finding stats using tables from information_schema database. Last 2 queries have been borrowed from
// http://forge.mysql.com/tools/tool.php?id=119
-- Total rows and size
SELECT COUNT(*) TABLES,
CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
FROM information_schema.TABLES
-- Database wise report
SELECT COUNT(*) TABLES, table_schema,
CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC
-- Engine wise breakup
SELECT ENGINE, COUNT(*) TABLES,
CONCAT(ROUND(SUM(table_rows) / 1000000,2),'M') rows,
CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024),2), 'G') DATA,
CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2),'G') idx,
CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
ROUND(SUM(index_length) / SUM(data_length),2) idxfrac
FROM information_schema.TABLES
GROUP BY engine ORDER BY sum(data_length+index_length) DESC
-- Top 30 tables
SELECT table_schema, table_name, engine, table_rows,
CONCAT(ROUND(data_length / (1024 * 1024),2),'MB') AS DATA,
CONCAT(ROUND((data_length + index_length) / (1024 * 1024), 2),'MB') AS total_size
FROM INFORMATION_SCHEMA.TABLES
ORDER BY data_length DESC LIMIT 30
-- List of Full Text Indexes
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY, NULLABLE
FROM statistics
WHERE index_type LIKE 'FULLTEXT%' ORDER BY TABLE_SCHEMA, TABLE_NAME
-- The number of columns for each datatype
SELECT DATA_TYPE, COUNT(*) AS mycount
FROM `COLUMNS`
WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql')
GROUP BY DATA_TYPE ORDER BY mycount DESC
-- The number of columns for each datatype with size (replace my_database)
SELECT TABLE_SCHEMA, COLUMN_TYPE, count(*) FROM `COLUMNS`
GROUP BY COLUMN_TYPE HAVING TABLE_SCHEMA = 'my_database'
-- Blob, Float and Double Data types details
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT,
IS_NULLABLE, NUMERIC_PRECISION, COLUMN_TYPE, COLUMN_KEY
FROM COLUMNS
WHERE DATA_TYPE IN ('FLOAT','BLOB','DOUBLE')
AND TABLE_SCHEMA != 'mysql'
ORDER BY DATA_TYPE, COLUMN_TYPE
--All tables with no primary key
use INFORMATION_SCHEMA;
select CONCAT(t.table_name,".",t.table_schema) as tbl,
from INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
ON (t.TABLE_NAME=c.TABLE_NAME
AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA
AND constraint_name='PRIMARY')
WHERE t.table_schema!="information_schema"
AND constraint_name IS NULL;
-- All tables and their primary keys, if exist:
use INFORMATION_SCHEMA;
select CONCAT(t.table_name,".",t.table_schema) as tbl,
c.column_name,c.constraint_name
from INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
ON (t.TABLE_NAME=c.TABLE_NAME
AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA
AND constraint_name='PRIMARY')
WHERE t.table_schema!="information_schema"
order by constraint_name;
-- Foreign Key constraints for Database dbName
SELECT A.TABLE_SCHEMA AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME,
A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,
A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.CONSTRAINT_NAME AS FK_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A, INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL
HAVING PKTABLE_SCHEM IS NOT NULL
and A.TABLE_SCHEMA = 'dbName'
ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION limit 1000
 Read more: |
|
