Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
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.
Check and correct a view's algorithm. This statement is primarily used by upgrade scripts to ensure view definitions are compatible.
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 , 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 and will be . 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
CHECK TABLEAs an alternative, myisamchk is a command-line 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 is 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 .
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 have changed since the 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 the 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 exist, 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 shut down 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.
In newer MariaDB versions, the EXTENDED clause provides more information:
This page is licensed: GPLv2, originally from fill_help_tables.sql
ANALYZE uses histograms, which can provide a better selectivity than InnoDB statistics offer. InnoDB statistics work with a limited sample set and are therefore not as accurate as persistent statistics can be. For more information on how the analysis works within InnoDB, see InnoDB 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.
When using ANALYZE TABLE PERSISTENT, MariaDB skips long CHAR/VARCHAR columns during statistics collection if they exceed the value of the analyze_max_length system variable.
This prevents excessive disk usage when analyzing tables with large text columns.
If a column is longer than analyze_max_length, it is excluded from stats.
If a long column is explicitly specified in FOR COLUMNS(), it is still analyzed, regardless of its size.
Example:
The functionality for skipping long CHAR and VARCHAR columns isn't available.
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,
when the distribution of data in a table has changed significantly, for example, if you have loaded a batch of data or deleted a large amount of data.
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 table statistics (EITS). See Engine-Independent Table Statistics: Collecting Statistics with the ANALYZE TABLE Statement for more information.
You can run the statement on all columns with this statement — however, be aware that this can take a long time for very large (500+ GB) tables:
Focusing on particular columns, the statement looks like this — it collects statistics only for the specified table columns:
Focusing on columns helps, among other things, avoid including BLOB columns, for which MariaDB doesn't collect statistics.
Queries that benefit most are those where the query plan depends on the optimizer knowing condition selectivities, the most important ones being JOIN queries and those with an ORDER BY ... LIMIT clause. To benefit, the query must have a condition that may or may not be selective, for example:
Here, the optimizer benefits from knowing these statistics:
Which fraction of customers are in the EMEA region?
Which fraction of orders are URGENT?
For that situation, you can issue this statement:
EITS (engine-independent table statistics) provide way more data than InnoDB-internal statistics. The downside is that EITS are never automatically updated, and it takes time to collect them.
InnoDB statistics, on the other hand, provide less data, but they are automatically updated.
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 (allowing you to turn InnoDB statistics on or off). 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.
MariaDB 10.6.23 / 10.11.14 / 11.4.8 / 11.8.3 / 12.0.2 / 12.1.1
Prevents the collection of column statistics for / columns that can be analyzed automatically by ANALYZE TABLE PERSISTENT. Columns exceeding this threshold in bytes will be skipped unless included explicitly in FOR COLUMNS(...).
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
This one trick can make MariaDB 30x faster! (mariadb.org blog)
This page is licensed: GPLv2, originally from fill_help_tables.sql
This page is licensed: CC BY-SA / Gnu FDL
CHECK VIEW view_nameRESTORE TABLE was removed and is no longer a part of MariaDB.
RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'Like , this command was not reliable and has been removed from MariaDB. For doing a backup of MariaDB use , or . See .
RESTORE TABLE restores the table or tables from a backup that was made with . 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 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
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}CHECK TABLE y EXTENDED;
+--------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.y | check | status | OK |
+--------+-------+----------+----------+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 |
+---------+-------+----------+--------------------------------------------------------------------------------------------------+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 tbl PERSISTENT FOR ALLANALYZE TABLE tbl PERSISTENT FOR COLUMS (column1, column2, ...) INDEXES (index1, ...)SELECT *
FROM orders, customer
WHERE
orders.o_custkey = customer.c_custkey -- join condition
AND customer.c_area = 'EMEA' -- (1)
AND orders.o_priority= 'URGENT' -- (2)ANALYZE TABLE orders, customer PERSISTENT FOR ALL-- 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;REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as
or
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 for this statement.
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]REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM] [FORCENumber 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 an admin in the 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.
Report a checksum for table contents. This statement calculates a value to compare tables, useful for verifying replication consistency.
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 ; currently, this is supported only for and 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 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 NULLs 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 to . 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 with the 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
myisamchk --recover tbl_namearia_chk --recover tbl_nameSET GLOBAL analyze_max_length = 50000;
ANALYZE TABLE large_text_table PERSISTENT;ANALYZE TABLE large_text_table PERSISTENT FOR COLUMNS(long_description);CREATE TABLE product_data (
id INT PRIMARY KEY,
name VARCHAR(100),
description VARCHAR(50000), -- long column
specs VARCHAR(1000)
);
-- Set limit
SET SESSION analyze_max_length = 10000;
-- Run analysis without explicitly selecting columns
ANALYZE TABLE product_data PERSISTENT;
-- 'description' will be skipped due to length > 10000
-- To include it anyway
ANALYZE TABLE product_data PERSISTENT FOR COLUMNS(description);CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]Browse documentation for removed table commands. This section covers obsolete statements like BACKUP TABLE and RESTORE TABLE.
Empty a table completely. This DDL statement drops and re-creates the table, offering a faster alternative to DELETE for removing all rows.
TRUNCATE TABLE empties a table completely. It requires the DROP privilege. See .
tbl_name can also be specified in the form db_name.tbl_name (see ).
Logically, TRUNCATE TABLE is equivalent to a statement that deletes all rows, but there are practical differences under some circumstances.
TRUNCATE TABLE will fail for an if any FOREIGN KEY constraints from other tables reference the table, returning the error:
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 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."
For the purposes of binary logging and , TRUNCATE TABLE is treated as followed by (DDL rather than DML).
TRUNCATE TABLE does not work on . Currently, TRUNCATE TABLE drops all historical records from a .
Set the lock wait timeout. See .
permits the optional keywords REUSE STORAGE or DROP STORAGE to be used.
These have no effect on the operation.
TRUNCATE TABLE is faster than , because it drops and re-creates a table.
With , TRUNCATE TABLE is slower if is set (the default). This is because TRUNCATE TABLE unlinks the underlying tablespace file, which can be an expensive operation. See for more details.
The performance issues with can be exacerbated in cases where the is very large and is set. In that case, using followed by instead of TRUNCATE TABLE may perform better. Setting can also help.
Setting can also improve TRUNCATE TABLE performance in general. See for more details.
system variable
This page is licensed: GPLv2, originally from
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 , this command was not reliable and has been removed in current versions of MariaDB.
For doing a backup of MariaDB use or . See .
This page is licensed: CC BY-SA / Gnu FDL
TRUNCATE [TABLE] tbl_name
[WAIT n | NOWAIT]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.
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraintTRUNCATE [TABLE] tbl_name [{DROP | REUSE} STORAGE] [WAIT n | NOWAIT]