Learn about table-related SQL statements in MariaDB Server. This section covers commands for creating, altering, dropping, and manipulating tables, essential for managing your database schema.
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [,tbl_name ...]
[PERSISTENT FOR
{ ALL
| COLUMNS ([col_name [,col_name ...]]) INDEXES ([index_name [,index_name ...]])
}
]
ANALYZE TABLE
analyzes and stores the key distribution for a table (index statistics). This statement works with MyISAM, Aria and InnoDB tables. During the analysis, InnoDB will allow reads/writes, and MyISAM/Aria reads/inserts. For MyISAM tables, this statement is equivalent to using myisamchk --analyze.
ANALYZE
uses histograms, which can provide a better selectivity than InnoDB statistics offer. InnoDB statistics work with a limited sample set and is therefore not as accurate as persistent statistics can be. For more information on how the analysis works within InnoDB, seeInnoDB Limitations.
MariaDB uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant. In addition, key distributions can be used when deciding which indexes to use for a specific table within a query.
This statement requires SELECT and INSERT privileges for the table.
By default, ANALYZE TABLE statements are written to the binary log and will be replicated. The NO_WRITE_TO_BINLOG
keyword (LOCAL
is an alias) will ensure the statement is not written to the binary log.
ANALYZE TABLE
statements are not logged to the binary log if read_only is set. See also Read-Only Replicas.
ANALYZE TABLE
is non-blocking and non-intrusive. A connection will start using new statistics for the query following the completion of the ANALYZE TABLE
.
ANALYZE TABLE
is blocking and intrusive.
ANALYZE TABLE
is also supported for partitioned tables. You can use ALTER TABLE
... ANALYZE PARTITION
to analyze one or more partitions.
The Aria storage engine supports progress reporting for the ANALYZE TABLE
statement.
Note that analyzing tables with ANALYZE
can have a performance impact and can use a lot of disk space for big tables. As column statistics usually do not change much over time, even when the table grows, there is no benefit to running ANALYZE
very often.
Running ANALYZE
is indicated:
for newly populated tables,
for tables that have additional columns added that are used in WHERE clauses,
when a table has doubled in size,
when you note that a query becomes slow because the table order has changed and you can see from EXPLAIN or ANALYZE FORMAT=JSON that the selectivity is wrong for a table.
ANALYZE
isn’t useful for table columns of type UNIQUE
, PRIMARY KEY
, TIME
, or CURRENT_TIME
. In ANALYZE
queries, you should omit columns of those types.
ANALYZE TABLE
supports engine-independent statistics. See Engine-Independent Table Statistics: Collecting Statistics with the ANALYZE TABLE Statement for more information.
Engine-independent statistics can be controlled (enabled and disabled) using the use_stat_tables variable and the optimizer_use_condition_selectivity variable. InnoDB-persistent statistics are controlled with the innodb_stats_persistent variable. Combining both kinds of statistics is possible.
The server relies on InnoDB statistics by default. That way, it can use some statistics even if ANALYZE TABLE
is never run (or not often enough). This gives good enough results for the majority of queries. Some queries, however, need more statistical data so the optimizer can create a good plan. Slow queries indicate there aren't enough statistical data. Those queries can be accelerated by running ANALYZE TABLE tbl PERSISTENT FOR ...
, where tbl
indicates a table used by a slow query. You can also run ANALYZE TABLE ... PERSISTENT FOR ALL
, but that has a significant performance impact.
The following overview indicates when a particular variable was introduced. When multiple versions are given, it means variable options (like the default value) changed between the indicated versions.
10.4.3
Percentage of rows from the table ANALYZE TABLE will sample to collect table statistics. Set to 0 to let MariaDB decide what percentage of rows to sample.
10.4.3-11.0
Specifies the type of histograms created by ANALYZE. Options are #SINGLE_PREC_HB,DOUBLE_PREC_HB or JSON_HB.
10.7
Number of bytes or buckets (in case of JSON_HB) used for storing the histogram. If set to 0, no histograms are created by ANALYZE.]]
11.0.1
Log slow OPTIMIZE, ANALYZE, ALTER and other administrative statements to the slow log if it is open. Deprecated. Use log_slow_filter instead.
(all versions)
Having admin in log_slow_filter will add slow ANALYZE_TABLE statements to the slow log.
(all versions)
For calculating the number of duplicates, ANALYZE TABLE uses a buffer of sort_buffer_size bytes per column. You can slightly increase the speed of ANALYZE TABLE by increasing this variable.
-- update all engine-independent statistics for all columns and indexes
ANALYZE TABLE tbl PERSISTENT FOR ALL;
-- update specific columns and indexes:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES (idx1,idx2,...);
-- empty lists are allowed:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES ();
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES (idx1,idx2,...);
-- the following will only update mysql.table_stats fields:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES ();
-- when use_stat_tables is set to 'COMPLEMENTARY' or 'PREFERABLY',
-- a simple ANALYZE TABLE collects engine-independent statistics for all columns and indexes.
SET SESSION use_stat_tables='COMPLEMENTARY';
ANALYZE TABLE tbl;
This one trick can make MariaDB 30x faster! (mariadb.org blog)
This page is licensed: GPLv2, originally from fill_help_tables.sql
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
CHECK TABLE
checks a table or tables for errors. CHECK TABLE
works for Archive, Aria, CSV, InnoDB, MyISAM and, from MariaDB 12.0, Sequence, tables. For Aria and MyISAM tables, the key statistics are updated as well. For CSV, see also Checking and Repairing CSV Tables.
As an alternative, myisamchk is a commandline tool for checking MyISAM tables when the tables are not being accessed. For Aria tables, there is a similar tool: aria_chk.
For checking dynamic columns integrity, COLUMN_CHECK() can be used.
CHECK TABLE
can also check views for problems, such as tables that are referenced in the view definition that no longer exist.
CHECK TABLE
is also supported for partitioned tables. You can use ALTER TABLE ... CHECK PARTITION
to check one or more partitions.
The meaning of the different options are as follows - note that this can vary a bit between storage engines:
FOR UPGRADE
Do a very quick check if the storage format for the table has changed so that one needs to do a REPAIR
. This is only needed when one upgrades between major versions of MariaDB or MySQL. This is usually done by running mariadb-upgrade.
FAST
Only check tables that has not been closed properly or are marked as corrupt. Only supported by the MyISAM and Aria engines. For other engines the table is checked normally
CHANGED
Check only tables that has changed since last REPAIR
/ CHECK
. Only supported by the MyISAM and Aria engines. For other engines the table is checked normally.
QUICK
Do a fast check. For MyISAM and Aria, this means skipping the check of the delete link chain, which may take some time.
MEDIUM
Scan also the data files. Checks integrity between data and index files with checksums. In most cases this should find all possible errors.
EXTENDED
Does a full check to verify every possible error. For InnoDB, Aria, and MyISAM, verify for each row that all its keys exists, and for those index keys, they point back to the primary clustered key. This may take a long time on large tables.
The EXTENDED
option is available to InnoDB, too.
The EXTENDED
option is ignored by InnoDB.
When the EXTENDED
option is given, MariaDB also checks for referential integrity.
MariaDB does not check for referential integrity, even if the EXTENDED
option is given.
For most cases, running CHECK TABLE
without options or MEDIUM
should be good enough.
The Aria storage engine supports progress reporting for this statement.
If you want to know if two tables are identical, take a look at CHECKSUM TABLE.
If CHECK TABLE
finds an error in an InnoDB table, MariaDB might shutdown to prevent the error propagation. In this case, the problem will be reported in the error log. Otherwise the table or an index might be marked as corrupted, to prevent use. This does not happen with some minor problems, like a wrong number of entries in a secondary index. Those problems are reported in the output of CHECK TABLE
.
Each tablespace contains a header with metadata. This header is not checked by this statement.
During the execution of CHECK TABLE
, other threads may be blocked.
CHECK TABLE y EXTENDED;
+--------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.y | check | status | OK |
+--------+-------+----------+----------+
In newer MariaDB versions, the EXTENDED
clause provides more information:
CHECK TABLE t1 EXTENDED;
+---------+-------+----------+----------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+----------------------------------------------------------------------+
| test.t1 | check | Warning | No suitable key found for foreign key t2_ibfk_1 in table test.t1 |
+---------+-------+----------+----------------------------------------------------------------------+
CHECK TABLE t2 EXTENDED;
+---------+-------+----------+--------------------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+--------------------------------------------------------------------------------------------------+
| test.t2 | check | status | Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2') |
| test.t2 | check | status | Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2') |
| test.t2 | check | status | Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '3') |
| test.t2 | check | error | Corrupt |
+---------+-------+----------+--------------------------------------------------------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
CHECK VIEW view_name
The CHECK VIEW
statement checks whether the view algorithm is correct. It is run as part of mariadb-upgrade, and should not normally be required in regular use.
This page is licensed: CC BY-SA / Gnu FDL
CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]
CHECKSUM TABLE
reports a table checksum. This is very useful if you want to know if two tables are the same (for example on a master and a replica).
With QUICK
, the live table checksum is reported if it is available, or NULL
otherwise. This is very fast. A live checksum is enabled by specifying the CHECKSUM=1
table option when you create the table; currently, this is supported only for Aria and MyISAM tables.
With EXTENDED
, the entire table is read row by row and the checksum is calculated. This can be very slow for large tables.
If neither QUICK
nor EXTENDED
is specified, MariaDB returns a live checksum if the table storage engine supports it and scans the table otherwise.
CHECKSUM TABLE
requires the SELECT privilege for the table.
For a nonexistent table, CHECKSUM TABLE
returnsNULL
and generates a warning.
The table row format affects the checksum value. If the row format changes, the checksum will change. This means that when a table created with a MariaDB/MySQL version is upgraded to another version, the checksum value will probably change.
Two identical tables should always match to the same checksum value; however, also for non-identical tables there is a very slight chance that they will return the same value as the hashing algorithm is not completely collision-free.
Identical tables mean that the CREATE statement is identical and that the following variable, which affects the storage formats, was the same when the tables were created:
CHECKSUM TABLE
may give a different result as MariaDB doesn't ignore NULL
s in the columns like MySQL 5.1 does (later MySQL versions should calculate checksums the same way as MariaDB).
You can get the 'old style' checksum in MariaDB by setting old_mode to COMPAT_5_1_CHECKSUM. Note, however, that the MyISAM and Aria storage engines in MariaDB are using the new checksum internally, so if you are using this old mode, the CHECKSUM
command will be slower as it needs to calculate the checksum row by row.
You can get the 'old style' checksum in MariaDB by starting mariadbd with the--old option. Note, however, that the MyISAM and Aria storage engines in MariaDB are using the new checksum internally, so if you are using this old mode, the CHECKSUM
command will be slower as it needs to calculate the checksum row by row.
This page is licensed: GPLv2, originally from fill_help_tables.sql
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM] [FORCE
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM]
REPAIR TABLE
repairs a possibly corrupted table. By default, it has the same effect as
myisamchk --recover tbl_name
or
aria_chk --recover tbl_name
See aria_chk and myisamchk for more.
REPAIR TABLE
works for Archive, Aria, CSV, and MyISAM tables. For InnoDB, see recovery modes. For CSV, see also Checking and Repairing CSV Tables. For Archive, this statement also improves compression. If the storage engine does not support this statement, a warning is issued.
This statement requires SELECT and INSERT privileges for the table.
By default, REPAIR TABLE
statements are written to the binary log and will be replicated. The NO_WRITE_TO_BINLOG
keyword (LOCAL
is an alias) will ensure the statement is not written to the binary log.
REPAIR TABLE
statements are not logged to the binary log if read_only is set. See also Read-Only Replicas.
REPAIR TABLE
statements are logged to the binary log.
When an index is recreated, the storage engine may use a configurable buffer in the process. Incrementing the buffer speeds up the index creation. Aria and MyISAM allocate a buffer whose size is defined by aria_sort_buffer_size or myisam_sort_buffer_size, also used for ALTER TABLE.
When specified, REPAIR TABLE
will not modify the data file, only attempting to repair the index file. The same behavior can be achieved with myisamchk --recover --quick.
Creates the index row by row rather than sorting and creating a single index. Similar to myisamchk --safe-recover.
For use only when the index file is missing or its header corrupted. MariaDB then attempts to recreate it using the .frm
file. There is no equivalent myisamchk option.
The FORCE
argument allows to first run internal repair to fix damaged blocks, and then follow it up with ALTER TABLE
(MDEV-33449).
The FORCE
option is not available.
REPAIR TABLE
is also supported for partitioned tables with the ALTER TABLE ... REPAIR PARTITION statement. However, the USE_FRM
option cannot be used with this statement on a partitioned table. See Repairing Partitions for details.
The Aria storage engine supports progress reporting for this statement.
This page is licensed: GPLv2, originally from fill_help_tables.sql
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] VIEW view_name[, view_name] ... [FROM MYSQL]
The REPAIR VIEW
statement checks whether the view algorithm is correct. It is run as part of mariadb-upgrade, and should not normally be required in regular use.
By default it corrects the checksum and if necessary adds the mariadb-version field. If the optional FROM MYSQL
clause is used, and no mariadb-version field is present, the MERGE
and TEMPTABLE
algorithms are toggled.
By default, REPAIR VIEW
statements are written to the binary log and will be replicated. The NO_WRITE_TO_BINLOG
keyword (LOCAL
is an alias) will ensure the statement is not written to the binary log.
This page is licensed: CC BY-SA / Gnu FDL
TRUNCATE [TABLE] tbl_name
[WAIT n | NOWAIT]
TRUNCATE TABLE
empties a table completely. It requires the DROP
privilege. See GRANT.
tbl_name
can also be specified in the form db_name
.tbl_name
(see Identifier Qualifiers).
Logically, TRUNCATE TABLE
is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.
TRUNCATE TABLE
will fail for an InnoDB table if any FOREIGN KEY constraints from other tables reference the table, returning the error:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
Foreign Key constraints between columns in the same table are permitted.
For an InnoDB table, if there are no FOREIGN KEY
constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one. The AUTO_INCREMENT counter is reset by TRUNCATE TABLE
, regardless of whether there is a FOREIGN KEY
constraint.
The count of rows affected by TRUNCATE TABLE
is accurate only when it is mapped to a DELETE
statement.
For other storage engines, TRUNCATE TABLE
differs fromDELETE
in the following ways:
Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.
Truncate operations cause an implicit commit.
Truncation operations cannot be performed if the session holds an active table lock.
Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is "0 rows affected," which should be interpreted as "no information."
As long as the table format file tbl_name.frm
is valid, the table can be re-created as an empty table with TRUNCATE TABLE
, even if the data or index files have become corrupted.
The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.
When used with partitioned tables, TRUNCATE TABLE
preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions (.par) file is unaffected.
Since truncation of a table does not make any use of DELETE
, the TRUNCATE
statement does not invoke ON DELETE
triggers.
TRUNCATE TABLE
will only reset the values in the Performance Schema summary tables to zero or null, and will not remove the rows.
For the purposes of binary logging and replication, TRUNCATE TABLE
is treated as DROP TABLE followed by CREATE TABLE (DDL rather than DML).
TRUNCATE TABLE
does not work on views. Currently, TRUNCATE TABLE
drops all historical records from a system-versioned table.
Set the lock wait timeout. See WAIT and NOWAIT.
Oracle-mode permits the optional keywords REUSE STORAGE
or DROP STORAGE
to be used.
TRUNCATE [TABLE] tbl_name [{DROP | REUSE} STORAGE] [WAIT n | NOWAIT]
These have no effect on the operation.
TRUNCATE TABLE
is faster than DELETE, because it drops and re-creates a table.
With InnoDB, TRUNCATE TABLE
is slower if innodb_file_per_table=ON is set (the default). This is because TRUNCATE TABLE
unlinks the underlying tablespace file, which can be an expensive operation. See MDEV-8069 for more details.
The performance issues with innodb_file_per_table=ON can be exacerbated in cases where the InnoDB buffer pool is very large and innodb_adaptive_hash_index=ON is set. In that case, using DROP TABLE followed by CREATE TABLE instead of TRUNCATE TABLE
may perform better. Setting innodb_adaptive_hash_index=OFF can also help.
Setting innodb_adaptive_hash_index=OFF can also improve TRUNCATE TABLE
performance in general. See MDEV-16796 for more details.
innodb_safe_truncate system variable
This page is licensed: GPLv2, originally from fill_help_tables.sql
Table commands that were removed from MariaDB.
BACKUP TABLE
was removed and is no longer a part of MariaDB
BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'
Note: Like RESTORE TABLE, this command was not reliable and has been removed in current versions of MariaDB.
For doing a backup of MariaDB use mysqldump or MariaDB Backup. See Backing Up and Restoring.
This page is licensed: CC BY-SA / Gnu FDL
RESTORE TABLE
was removed and is no longer a part of MariaDB.
RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'
Like BACKUP TABLE, this command was not reliable and has been removed from MariaDB. For doing a backup of MariaDB use mysqldump, mysqlhotcopy or XtraBackup. See Backing Up and Restoring.
RESTORE TABLE
restores the table or tables from a backup that was made with BACKUP TABLE. The
directory should be specified as a full path name.
Existing tables are not overwritten; if you try to restore over an existing table, an error occurs. Just as for BACKUP TABLE
,RESTORE TABLE
works only for MyISAM tables. Restored tables are not replicated from master to slave.
The backup for each table consists of its .frm format file and .MYD
data file. The restore operation restores those files, and then uses them to rebuild the .MYI
index file. Restoring takes longer than
backing up due to the need to rebuild the indexes. The more indexes the table has, the longer it takes.
This page is licensed: GPLv2, originally from fill_help_tables.sql