Skip to main content

MySQL

Show tables size​

select 
    table_schema as `Database`, 
    table_name as `Table`, 
    round(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) `Size (MiB)` 
from INFORMATION_SCHEMA.TABLES;

Calculate age​

-- <birth> = YYYY-MM-DD
select timeStampDiff(year, <birth>, curDate()) as age;

Find in set​

select 
  find_in_set('B', 'A,B,C'), 
  find_in_set('D', 'A,B,C'); 
-- Return 2, 0

Insert large number of row​

Reference

Use START TRANSACTION and COMMIT

Recovery​

Reference

Edit option file

[mysqld]
innodb_force_recovery = 1

If the log loops with

InnoDB: Waiting for the background threads to start

Edit option file

[mysqld]
innodb_purge_threads = 0

Check tables​

mysqlcheck [{ -c | --check }] [-u <user>] [-p] <database> [<table> [...]]

or

check table <table> [, ...]

Repair tables​

mysqlcheck [{ -r | --repair }] [-u <user>] [-p] <database> [<table> [...]]

Rebuild / Reorganize index​

optimize table <table> [, ...];

Config file​

Reference

/etc/my.cnf
# <comment>
; <comment>

[<group>]

[mysqld]
# Option for mysqld command

[mysql]
# Option for mysql command

[mysqladmin]
# Option for mysqladmin command

[client]
# Option for client (mysql, mysqldump, ...)

# port=<port>
port=3306

# password=<password>
password=This15Password

<option>
# equivalent to "--<option>" on the command line

<option>=<value>
# equivalent to "--<option>=<value>" on the command line

!include <file>
# include config file

!includedir <dir>
# include config file "*.cnf" in <dir>

Help​

{ help | ? } [<command>];

Explain​

Reference

Optimizing query

explain { select ... | delete ... | ... }

Database​

create database​

create database <database>;

drop database​

drop database <database>;

Table​

list tables​

show tables;

show table structure​

describe <table>;

show create table statement​

show create table <table>;

Display results vertically​

Use \G instead of ; semicolon