Just listing a few useful MySQL Queries I use all the time to maintain my databases. They are all mainly about keeping track of database size and performance.

The following gem returns all databases and their physical sizes and free space available. querying information_schema in this manner is always slow so watch out when you run this.

SELECT
  table_schema db_name,
  sum( data_length + index_length ) / 1024 / 1024 db_size_MB,
  sum( data_free )/ 1024 / 1024 free_MB
FROM information_schema.TABLES
GROUP BY table_schema;

The following gem returns the top 10 largest tables across all databases. I found it in the MySQL Performance blog, and it’s safe to say I use it all the time. The query is quite heavy on large databases, and may take a while to return your results as the above, so don’t use it in the middle of the day during business hours unless you want your sites to run slowly.

SELECT
  CONCAT(table_schema, '.', table_name) table_name,
  CONCAT(ROUND(table_rows / 1000000, 2), 'M') total_rows,
  CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') total_data,
  CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') total_index,
  CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
  CONCAT(ROUND(data_free / (1024 * 1024), 2), 'MB') total_free,
  ROUND(index_length / data_length, 2) index_to_data_ratio
FROM
  information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;

The following lists all collation and character sets of all tables on all databases.

SELECT
  CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) 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
  TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA')
ORDER BY TABLE_SCHEMA ASC, TABLE_NAME ASC

Lists active processes. (MySQL > 5.1)

SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep'

Slow running queries. (MySQL > 5.1) You may kill any processes with the “KILL” command and the ID

SELECT
   ID, INFO, USER, HOST, TIME
FROM information_schema.PROCESSLIST
WHERE
  COMMAND != 'Sleep' AND TIME > 4
ORDER BY TIME DESC;