MySQL

Status and Information

show global status;
show table status like 'tablename' \G;
show processlist;

flush status;
-- execute query
show session status like 'Select%'
show session status like 'Handler%';
show session status like 'Create%';
show session status like 'Sort%';

explain select ...
show create table tablename

Table Sizes

SELECT table_name "table", sum( data_length + index_length ) / 1024 / 1024 "mb" FROM
information_schema.TABLES GROUP BY table_name order by mb

Storage Engine

To see a table's storage engine:

SELECT ENGINE FROM information_schema.TABLES where table = 'tablename'

To convert to InnoDB:

ALTER TABLE tablename ENGINE=InnoDB;

Edit my.cnf to make all tables InnoDB:

[mysqld]
default-storage-engine=innodb

Profiling

set profiling = 1;
-- execute query
show profile;
show profiles;
show profile cpu for query 1;

Users and Permissions

create user username identified by 'pass'
grant all on db.* to username@localhost;