MySQL Tables

Databases

MySQL

MySQL Schema

Check

SHOW DATABASES;

SHOW TABLES;

DESCRIBE myTable;

SHOW CREATE TABLE myTable;

SHOW INDEXES FROM myTable;

mysqlshow

mysqlshow myDatabase

mysqlshow myDatabase myTable

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'myDatabase/myTable' \G

SHOW TABLE STATUS LIKE 'mytable'\G

Unused Space

This is visible as Data_free in the output of SHOW TABLE STATUS. You can show data for all tables using the query below...

SELECT table_name,

FORMAT(data_length/POWER(1024,2),0) AS table_noidx_MB,

FORMAT(SUM(data_length+index_length)/POWER(1024,2),0) AS table_size_MB,

FORMAT(data_free/POWER(1024,2),0) AS table_free_MB,

FORMAT(SUM(data_length+index_length)/POWER(1024,3),0) AS table_size_GB,

FORMAT(data_free/POWER(1024,3),0) AS table_free_GB

FROM information_schema.tables

WHERE table_schema='alfresco_uat'

GROUP BY table_name

ORDER BY data_free DESC;

To reclaim space you could Optimize the table (see later on this page).

NOTE: data_free isn't a great measure. Certainly it excludes any free space in indexes on the table (i.e. if you optimize the table you may find that the size on disk shrinks by more than the amount of data_free... unless data_free is larger than data_length+index_length, which most likely indicates that, at some point, a large delete has happened).

Create

CREATE TABLE myTable (myVarcharCol VARCHAR(20), myCharCol CHAR(1), myDateCol DATE);

A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed...

CREATE TEMPORARY TABLE myTable (myVarcharCol VARCHAR(20), myCharCol CHAR(1), myDateCol DATE);

An example including indexes and constraints...

CREATE TABLE myTable ( myInt INT AUTO_INCREMENT,

myVC1 VARCHAR(30) NOT NULL,

myVC2 VARCHAR(30) NOT NULL UNIQUE,

first VARCHAR(30),

last VARCHAR(30),

PRIMARY KEY(myInt),

UNIQUE(myVC1),

INDEX myVC2i(myVC2) INVISIBLE,

INDEX name(first, last) COMMENT 'first and/or last' );

MySQL Table Columns

CREATE TABLE

Data Types

MySQL Indexes

MySQL Temp Tables

Primary Key

TODO

Foreign Keys

FOREIGN KEY (orderid) REFERENCES orders(id)

In the background this auto generates a contraint name, and a foreign key name.

CONSTRAINT c_order FOREIGN KEY fk_order (orderid) REFERENCES orders(id)

If you don't like auto generated names you can give them your own names.

FOREIGN KEY (orderid) REFERENCES orders(id) ON UPDATE CASCADE ON DELETE CASCADE

If you delete an order from the orders table or update the id in the orders table, this will cascade the change (i.e. it will automatically delete or update the corresponding rows in the table containing this foreign key constraint.)

There may be a situation where you need to temporarily disable foreign key checks (maybe you want to load data into some child tables and create the parent records later). You can toggle foreign key constraint checks using the following commands...

SET foreign_key_checks = 0;

SET foreign_key_checks = 1;

NOTE: re-enabling foreign key checks does NOT verify any data added whilst they were disabled.

Unique Contraints

myVC2 VARCHAR(30) NOT NULL UNIQUE,

UNIQUE(myVC1),

As shown in the CREATE TABLE example above, a UNIQUE constraint can be inline...

... or added as a standalone constraint.

UNIQUE(first,last)

A constraint where a combination of two or more columns must be unique must be added standalone

CONSTRAINT c_firstlast UNIQUE(first,last)

You can, optionally, use your own name for the constraint.

Data Types

Numeric

SMALLINT

Bytes: 2
Min Signed: -32768
Max Signed: 32767
Min Unsigned: 0
Max Unsigned: 65535

TINYINT

Bytes: 1
Min Signed: -128
Max Signed: 127
Min Unsigned: 0
Max Unsigned: 255

MEDIUMINT

Bytes: 3
Min Signed: -8388608
Max Signed: 8388607
Min Unsigned: 0
Max Unsigned: 16777215

BIT

Up to 64 binary (0 or 1) digits.

INTEGER / INT

Bytes: 4
Min Signed: -2,147,483,648
Max Signed: 2,147,483,647
Min Unsigned: 0
Max Unsigned: 4,294,967,295

BIGINT

Bytes: 8
Min Signed: -9,223,372,036,854,775,808
Max Signed: 9,223,372,036,854,775,807
Min Unsigned: 0
Max Unsigned: 18,446,744,073,709,551,615

DECIMAL / DEC / FIXED / NUMERIC

Fixed decimal point type defined with Precision (before the decimal point) and Scale (after the decimal point).

Max digits: 65

FLOAT / REAL / DOUBLE / DOUBLE PRECISION / REAL

(REAL is a synonym for FLOAT only if REAL_AS_FLOAT SQL mode is enabled)

FLOAT(M,D)and DOUBLE(M,D) are nonstandard MySQL extensions; and are deprecated. Expect them to be removed in a future version of MySQL.

Date & Time

DATE

Range: '1000-01-01' to '9999-12-31'

DATETIME

Range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

TIMESTAMP

Range: '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

TIME

Range: '-838:59:59.000000' to '838:59:59.000000'

YEAR

Range: 1901 to 2155, and 0000

NOTE: TIMESTAMP and DATETIME can automatically be set to the current time using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in column definitions

MySQL uses a proleptic Gregorian calendar. Any dates before the Julian to Gregorian cutover will be represented as if the Gregorian calendar was already in use (in Great Britain this switch happened in September 1752)

String

CHAR

Max Length: 255 characters

VARCHAR

Max Length: 65535 characters

BINARY

Max Length: 255 bytes

VARBINARY

Max Length: 65535 bytes

ENUM

SET

TINYBLOB

BLOB

MEDIUMBLOB

LONGBLOB

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

Spatial

TODO

JSON

TODO

Views and Synonyms

MySQL Views

MySQL does not implement SYNONYMs but you can fake it using VIEWs.

Configuration

INNODB_DEFAULT_ROW_FORMAT

MySQL File Formats

MySQL Configuration

Default: DYNAMIC

SELECT @@innodb_default_row_format;

You can override the default row format during a CREATE or ALTER TABLE operation if required.

DYNAMIC - The DYNAMIC row format offers the same storage characteristics as the COMPACT row format but adds enhanced storage capabilities for long variable-length columns and supports large index key prefixes (1). Valid for Barracuda File Format only.
COMPACT - The COMPACT row format reduces row storage space by about 20% compared to the REDUNDANT row format, at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed, COMPACT format is likely to be faster. If the workload is limited by CPU speed, compact format might be slower (1). Valid for Antelope and Barracuda File Formats.
REDUNDANT - The REDUNDANT format provides compatibility with older versions of MySQL (1). Valid for Antelope and Barracuda File Formats.

The COMPRESSED row format, which is not supported for use in the system tablespace, cannot be defined as the default. The COMPRESSED row format offers the same storage characteristics and capabilities as the DYNAMIC row format but adds support for table and index data compression (1). Valid for Barracuda File Format only.

To see the row format of a specific table...

SELECT *
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE NAME LIKE 'mydb/mytable' \G

To ovverride the default row format at table level...

ALTER TABLE mytable ROW_FORMAT=COMPRESSED;

INNODB_FILE_PER_TABLE

MySQL Configuration

Default: 1 (ON)

The default has been 1 since MySQL 5.6.6

SELECT @@innodb_file_per_table;

This configuration option controls whether each table has its own .frm and .ibd files in your database directory.

1 - ON
0 - OFF

When innodb_file_per_table is OFF then all table data is stored in the ibdata1 file (i.e. individual .frm files will exist but .ibd files will not)

INNODB_CHECKSUM_ALGORITHM

MySQL Configuration

SELECT @@innodb_checksum_algorithm;

Specifies how to generate and verify the checksum stored in the disk blocks of InnoDB tablespaces. crc32 is the default value as of MySQL 5.7.7. (2)

LOWER_CASE_TABLE_NAMES

MySQL Configuration

SELECT @@lower_case_table_names;

This configuration option controls whether each table name is case-insensitive.

lower_case_table_names can only be configured when initializing the server.
Changing the lower_case_table_names setting after the server is initialized is prohibited. (3)

Load Data

To load data from a CSV file...

LOAD DATA INFILE '/tmp/myfile.csv' INTO TABLE myTable

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n';

For Windows use UNIX style forward slashes "/" or double up backslashes "\\"

Optimize

MySQL Optimizer

See: ANALYZE TABLE

Running Optimize regularly is not recommended.
Optimize only when there is likely to be a benefit. For example, if you have performed a bulk DELETE of many records (which you do not expect to be replaced).

To reclaim space in a table...

OPTIMIZE TABLE mytable;

You can provide a comma separated list of tables to optimize multiple tables in the same operation.

An alternative method...

sudo mysqlcheck -o mydatabase mytable -u myuser -p mypasswd

NOTE that this will temporarily create a copy of the table so be sure you have enough space.

NOTE: The table will be locked for writes for the duration of the optimize operation.

Info: OPTIMIZE is an alias for ALTER TABLE...FORCE on Innodb tables

Percona

If you have Percona tools installed you can use this to optimize tables without locking the table...

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydatabase, t=mytable --execute

Percona Toolkit

NOTE that this will temporarily create a copy of the table so be sure you have enough space.
NOTE pt-online-schema-schange avoids locking by creating triggers on the original table that insert, update or delete on the temporary copy of the table whilst the optimize is in progress.

Example

+--------------------------------+----------------+---------------+---------------+---------------+---------------+| table_name | table_noidx_MB | table_size_MB | table_free_MB | table_size_GB | table_free_GB |+--------------------------------+----------------+---------------+---------------+---------------+---------------+| mytable1 | 11 | 74 | 107 | 0 | 0 |+--------------------------------+----------------+---------------+---------------+---------------+---------------+

Based on this output we would expect the table size on disk to be ~181MB

ls -l /mysql/data/mydatabase/mytable1.ibd

-rw-rw---- 1 mysql mysql 197132288 Jun 1 20:02 /mysql/data/mydatabase/mytable1.ibd

In reality table size on disk is 197MB

optimize table mytable1;

+-----------------------+----------+----------+-------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+-----------------------+----------+----------+-------------------------------------------------------------------+| mydatabase.mytable1 | optimize | note | Table does not support optimize, doing recreate + analyze instead || mydatabase.mytable1 | optimize | status | OK |+-----------------------+----------+----------+-------------------------------------------------------------------+2 rows in set (1.13 sec)

This isn't suggesting anything is wrong, it's just highlighting that "optimize" on innodb works differently to "optimize" on MyISAM. See Bug #108101.

Bug #108101

+--------------------------------+----------------+---------------+---------------+---------------+---------------+| table_name | table_noidx_MB | table_size_MB | table_free_MB | table_size_GB | table_free_GB |+--------------------------------+----------------+---------------+---------------+---------------+---------------+| mytable1 | 4 | 14 | 0 | 0 | 0 |+--------------------------------+----------------+---------------+---------------+---------------+---------------+

Based on this output we would expect the new table size on disk to be ~14MB

ls -l /mysql/data/mydatabase/mytable1.ibd

-rw-rw---- 1 mysql mysql 14680064 Jun 1 20:18 /mysql/data/mydatabase/mytable1.ibd

Size on disk matches estimate... 14MB

Troubleshooting

Check for Errors

This applies to InnoDB. For MyISAM options refer to the documentation.https://dev.mysql.com/doc/refman/5.7/en/check-table.html
Note that running this command does carry a degree of risk... from blocked threads to server exit.https://dev.mysql.com/doc/refman/5.7/en/check-table.html#check-table-innodb

At its simplest...

CHECK TABLE myTable;

Do not scan the rows for incorrect links.

CHECK TABLE myTable QUICK;

Rebuilding or Repairing

https://dev.mysql.com/doc/refman/5.7/en/rebuilding-tables.html

To rebuild by dumping and reloading...

mysqldump myDatabase myTable > MyDumpFile.sql

mysql myDatabase < MyDumpFile.sql

To rebuild an InnoDB Table using ALTER TABLE...

ALTER TABLE myTable ENGINE = InnoDB;

The REPAIR TABLE command can be used for non InnoDB table types (MyISAM, ARCHIVE, CSV)

mysqldump

ERROR 2013 (HY000): Lost connection to MySQL server during query

If this happens during a DDL operation (e.g. ALTER TABLE) you could be left with some temporary tables identifiable (assuming you are using innodb_file_per_table) by a leading hash (#). To get rid of them you may need to do this (2)...

DROP TABLE`#mysql50##temporarytablename`;

Bibliography & References