2,187
edits
Difference between revisions of "MySQL"
Jump to navigation
Jump to search
m
→Database Sizes: Added Top Tables
(→SHOW etc: Added "Database Sizes") |
m (→Database Sizes: Added Top Tables) |
||
| Line 267: | Line 267: | ||
==== Database Sizes ==== | ==== Database Sizes ==== | ||
'''Databases''' | |||
<source lang="mysql"> | <source lang="mysql"> | ||
SELECT table_schema "Database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "Size (MB)" FROM information_schema.tables GROUP BY table_schema; | SELECT table_schema "Database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "Size (MB)" FROM information_schema.tables GROUP BY table_schema; | ||
</source> | </source> | ||
'''Top Tables''' | |||
Be sure to update the database name in the <code>FROM</code> line | |||
<source lang="mysql"> | |||
SELECT table_name, | |||
ROUND(table_rows / 1000, 2) AS 'rows (K)', | |||
ROUND(data_length / ( 1024 * 1024), 2) AS 'data (MB)', | |||
ROUND(index_length / ( 1024 * 1024), 2) AS 'index (MB)', | |||
ROUND(( data_length + index_length ) / ( 1024 * 1024), 2) AS 'total (MB)' | |||
FROM information_schema.TABLES WHERE table_schema = 'databasename' | |||
ORDER BY data_length + index_length DESC | |||
LIMIT 15; | |||
</source> | |||
Adapted from http://www.mysqlperformanceblog.com/2008/02/04/finding-out-largest-tables-on-mysql-server/ | |||
=== Corruption === | === Corruption === | ||