Miscellaneous SQL Queries

View the size on disk for all databases: SELECT table_schema AS "DATABASE NAME", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

Check if logging of slow queries is enabled: mysqladmin var | grep log_slow_queries

Enable logging of slow queries:

1. Open the MySQL config file: sudo vim /etc/mysql/my.cnf
2. In the mysqld section add:
   1. long_query_time = <time variable>
   2. log-slow-queries = /var/log/mysql/mysql-slow.log
3. Open the log file: sudo vim /var/log/mysql/mysql-slow.log

Optimize all tables in all databases: mysqlcheck -u USERNAME -p -o --all-databases