-- During the course of my daily work I occasionally search for mysql queries which are cool and helpful. I once found the following query on http://forge.mysql.com/:
SELECT TABLE_NAME article_attachment,
engine,
ROUND(data_length/1024/1024,2) total_size_mb,
ROUND(index_length/1024/1024,2) total_index_size_mb,
table_rows
FROM information_schema.tables
WHERE table_schema = 'MYDBNAME'
ORDER BY 3 DESC LIMIT 20;
-- Finding largest tables on MySQL instance is no brainier in MySQL 5.0+
-- thanks to Information Schema but I still wanted to post little query
-- I use for the purpose so I can easily find it later,
-- plus it is quite handy in a way it presents information
SELECT
CONCAT(table_schema, '.', TABLE_NAME),
CONCAT(ROUND(table_rows / 1000000, 2), 'M') ROWS,
CONCAT(
ROUND(data_length / (1024 * 1024 * 1024), 2),
'G'
) DATA,
CONCAT(
ROUND(index_length / (1024 * 1024 * 1024), 2),
'G'
) idx,
CONCAT(
ROUND(
(data_length + index_length) / (1024 * 1024 * 1024),
2
),
'G'
) total_size,
ROUND(index_length / data_length, 2) idxfrac
FROM
information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10 ;
-- Find total number of tables, rows, total data in index size for given MySQL Instance
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
WHERE TABLE_NAME LIKE "%performance_log%";
-- Find the same data using some filter
-- I often use similar queries to find space used by particular table "type" in sharded
-- environment when multiple tables with same structure and similar name exists:
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;
-- OR also
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
WHERE TABLE_NAME LIKE "%performance_log%";
-- Find biggest databases
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 LIMIT 10;
-- Data Distribution by Storage Engines
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 LIMIT 10;
Loading...