Mysql cheetsheet commands & script

Bookmark this so that do not have to search again and again .

Before start : You may get Sample database from here setup on your environment if you would like to .

Shchema Information

Show database (version) tables, create table, index

show status;
show global status ;
select version();
show variables like 'slow%';
show databases;
use database <db_name>
show tables;
desc employees;
show create table employees;
show indexes from employees;

Some table has lot of columns, It’s tedious to name them one by one (also may spelling wrongly) this is query to get all column names and saperated by comma .

select group_concat(column_name order by ordinal_position) as columns
from information_schema.columns
where table_schema = 'employees' and table_name = 'employees';

show all table size (plus index size) in database

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "employees"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;
+----------------------+-----------+
| Table                | Size (MB) |
+----------------------+-----------+
| salaries             |        96 |
| titles               |        20 |
| dept_emp             |        17 |
| employees            |        15 |
|…