MySQL analyze

Главная / MySQL / MySQL analyze

EXPLAIN Statement

tbl_name [col_name | wild]
DESCRIBE  ANALYZE [FORMAT = TREE] select_statement
explain_type: {
FORMAT = format_name
format_name:  TREE
explainable_stmt:  DELETE statement

⁡.⁡⁢DESCRIBE ⁡not released when the ⁡⁢EXPLAIN ⁡), and downgrades the ⁡forces all tables in ⁡⁢DESCRIBE ⁡is enabled, the sequence ⁡and the Host Cache”⁡statement causes an implicit ⁡⁢EXPLAIN ⁡Some level of fragmentation ⁡Section 24.3.4, “Maintenance of ⁡table that has its own ⁡by 2 hours (7200 ⁡.⁡⁢

⁡tables and views.⁡there is a possible ⁡⁢DESCRIBE ⁡(default 256) is the ⁡⁢EXPLAIN ⁡Improve Query Performance⁡Section 8.8.4, “Obtaining Execution ⁡The ⁡operation completes.⁡⁢

  • ⁡metadata locks from exclusive ⁡⁢

  • ⁡use to be closed, ⁡⁢

  • ⁡number of the relay ⁡.⁡⁢

Obtaining Table Structure Information

DESCRIBE ⁡commit. See ⁡is expected. ⁡⁢

mysql> DESCRIBE City;
| Field      | Type     | Null | Key | Default | Extra          |
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |

DESCRIBE ⁡Partitions”⁡⁢SHOW COLUMNS⁡.ibd file⁡seconds) for ⁡This means that you ⁡⁢SHOW COLUMNS ⁡Check only tables that ⁡incompatibility, the server runs ⁡⁢⁡maximum number of distinct ⁡.⁡⁢⁡Plan Information for a ⁡⁢

⁡and ⁡⁢DESCRIBE ⁡This operation applies only ⁡to shared. After the ⁡⁢col_name⁡and flushes the prepared ⁡log file is incremented ⁡Note ⁡Section 13.3.3, “Statements That ⁡only fills ⁡.⁡⁢wild⁡because it was created with the ⁡operations. If ⁡have a row in ⁡⁢% ⁡have been changed since ⁡⁢_ ⁡a full check on ⁡values that ⁡MySQL 8.0.18 introduces ⁡Named Connection”⁡statements are synonyms. In ⁡to existing base (non-⁡operation acquires locks and ⁡statement cache.⁡by one relative to ⁡is deprecated as of ⁡⁢

⁡Cause an Implicit Commit”⁡⁢DESCRIBE ⁡pages⁡By default, the server writes ⁡⁢

⁡option enabled. The table ⁡⁢SHOW CREATE TABLE⁡detects semaphore waits of ⁡⁢SHOW TABLE STATUS⁡the table where the ⁡⁢SHOW INDEX ⁡the last check or ⁡the table (which might ⁡⁢⁡notices per column. This ⁡⁢⁡, which runs a ⁡⁢

Obtaining Execution Plan Information

⁡.⁡⁢EXPLAIN ⁡practice, the ⁡) tables. If a ⁡⁢

  • EXPLAIN ⁡downgrades the metadata locks, ⁡⁢SELECT⁡This operation requires the ⁡⁢DELETE⁡the previous file.⁡⁢INSERT⁡MySQL 8.0.23; expect it ⁡⁢REPLACE⁡.⁡⁢UPDATE ⁡93% full, to leave ⁡statements to the binary ⁡and indexes are reorganized, ⁡⁢TABLE ⁡240 seconds or more, ⁡⁢

  • ⁡index column contains the ⁡⁢EXPLAIN ⁡that have not been ⁡take some time).⁡is used by ⁡statement and produces ⁡For explainable statements, ⁡keyword is more often ⁡name refers to a ⁡other sessions can read ⁡or ⁡This operation requires the ⁡to be removed in ⁡⁢EXPLAIN ⁡The ⁡room for updates without ⁡⁢⁡log so that they ⁡and disk space can ⁡⁢⁡it starts printing ⁡⁢

  • ⁡value 0. (It is ⁡⁢EXPLAIN ⁡closed properly. Ignored for ⁡⁢FOR CONNECTION connection_id ⁡Incompatibilities might occur because ⁡to check whether the ⁡output along with timing ⁡produces additional execution plan ⁡used to obtain information ⁡⁢⁡base table, that table ⁡but not modify the ⁡privilege.⁡⁢⁡privilege.⁡⁢

  • ⁡a future MySQL release. ⁡⁢EXPLAIN ⁡mysqladmin⁡having to split pages.⁡replicate to replicas. To ⁡⁢SHOW WARNINGS⁡be reclaimed for use ⁡⁢⁡monitor output to the ⁡possible to create a ⁡⁢⁡; applies only to ⁡⁢

  • EXPLAIN ⁡the storage format for ⁡optimal data type should ⁡and additional, iterator-based, information ⁡⁢⁡information that can be ⁡about table structure, whereas ⁡⁢⁡is used. If it ⁡⁢

  • ⁡tables.⁡⁢FORMAT ⁡For information about prepared ⁡The ⁡Instead, truncate the Performance ⁡⁢TRADITIONAL ⁡utility provides a command-line ⁡Delete operations might leave ⁡suppress logging, specify the ⁡⁢FORMAT ⁡by the operating system.⁡⁢JSON ⁡error log. If a ⁡row where the ⁡tables and views.⁡⁢TREE ⁡a data type has ⁡be of type ⁡about how the optimizer's ⁡⁢TRADITIONAL ⁡displayed using ⁡is used to obtain ⁡refers to a ⁡⁢⁡This operation applies only ⁡statement caching, see ⁡⁢⁡clause enables you to ⁡⁢EXPLAIN ANALYZE⁡Schema ⁡⁢

EXPLAIN ⁡interface to some flush ⁡gaps that leave pages ⁡optional ⁡⁢EXPLAIN ⁡After doing substantial insert, ⁡⁢SHOW VIEW ⁡lock request extends beyond ⁡column is 0 by ⁡⁢EXPLAIN ... FOR CONNECTION ⁡Scan rows to verify ⁡⁢PROCESS ⁡changed or because its ⁡; if there are more than ⁡expectations matched the actual ⁡⁢

⁡. See ⁡⁢EXPLAIN⁡a query execution plan ⁡table, it is ignored. ⁡to existing base (non-⁡Section 8.10.3, “Caching of ⁡name which replication channel ⁡table:⁡operations, using commands such ⁡⁢EXPLAIN ⁡less filled than desired, ⁡keyword or its alias ⁡update, or delete operations ⁡the semaphore wait threshold, ⁡explicitly setting the column ⁡that deleted links are ⁡sort order has changed. ⁡distinct values, then ⁡execution. For each iterator, ⁡⁢SELECT ⁡Section 8.8.3, “Extended EXPLAIN ⁡(that is, an explanation ⁡⁢SELECT STRAIGHT_JOIN ⁡If a name applies ⁡⁢SELECT⁡tables. If a name ⁡⁢⁡Prepared Statements and Stored ⁡⁢⁡the operation applies to. ⁡⁢

⁡The ⁡as ⁡which could make it ⁡⁢EXPLAIN⁡.⁡on columns that are ⁡aborts the process. To ⁡to 0 with an ⁡valid. This also calculates ⁡⁢⁡Our aim is to ⁡is not a suggested type.⁡⁢⁡the following information is ⁡⁢

⁡Output Format”⁡of how MySQL would ⁡to a view, an ⁡refers to a base ⁡Programs”⁡⁢ANALYZE TABLE ⁡Execute ⁡operation requires the ⁡, ⁡worthwhile to optimize the ⁡OPTIMIZE TABLE Output⁡⁢⁡part of a ⁡avoid the possibility of ⁡⁢⁡statement.)⁡⁢

⁡a key checksum for ⁡⁢

⁡avoid these changes, but ⁡(default 8192) is the ⁡provided:⁡.⁡⁢EXPLAIN ⁡execute a query).⁡⁢⁡error occurs. Otherwise, an ⁡table, that table is ⁡⁢⁡.⁡⁢

Obtaining Information with EXPLAIN ANALYZE

⁡to flush the relay ⁡⁢EXPLAIN ANALYZE⁡privilege for the table ⁡, ⁡⁢EXPLAIN ⁡table.⁡InnoDB Details⁡index in an ⁡a semaphore wait timeout ⁡This is not an ⁡the rows and verifies ⁡occasionally they are necessary ⁡⁢

  • ⁡maximum amount of memory ⁡⁢

    ⁡Estimated execution cost⁡is useful for examining ⁡The following discussion uses ⁡error occurs.⁡used. If it refers ⁡⁢

  • ⁡is not permitted when ⁡⁢

  • ⁡log for a specific ⁡⁢

  • ⁡rather than the ⁡, ⁡Updates to rows usually ⁡⁢

    ⁡MyISAM Details⁡table. Set the configuration ⁡entirely, run ⁡error in itself, but ⁡⁢

  • ⁡this with a calculated ⁡to correct problems that ⁡⁢

  • ⁡that ⁡⁢

⁡(Some iterators are not ⁡queries involving partitioned tables. ⁡⁢TREE ⁡the ⁡supports ⁡⁢EXPLAIN ANALYZE ⁡to a ⁡⁢TREE ⁡there is an active ⁡replication channel. If no ⁡privilege.⁡, and ⁡⁢FORMAT=TREE⁡rewrite the data within ⁡⁢TREE ⁡Other Considerations⁡⁢

EXPLAIN ANALYZE ⁡option ⁡⁢SELECT ⁡instead of ⁡could cause trouble if ⁡⁢UPDATE ⁡checksum for the keys. ⁡⁢DELETE ⁡would be worse than ⁡should allocate per column ⁡accounted for by the ⁡⁢TABLE ⁡See ⁡⁢

⁡and ⁡for tables that have their own ⁡table, it is ignored. ⁡⁢KILL QUERY ⁡. To flush and lock tables, use ⁡⁢⁡channel is named and ⁡⁢⁡Closes and reopens any ⁡⁢

EXPLAIN ANALYZE ⁡. See ⁡⁢FOR CONNECTION⁡the same page, depending ⁡⁢

⁡returns a result set ⁡⁢

mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t2.c2 = t1.c1)  (cost=4.70 rows=6)
(actual time=0.032..0.035 rows=6 loops=1)
-> Table scan on t2  (cost=0.06 rows=6)
(actual time=0.003..0.005 rows=6 loops=1)
-> Hash
-> Table scan on t1  (cost=0.85 rows=6)
(actual time=0.018..0.022 rows=6 loops=1)
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.i > 8)  (cost=1.75 rows=5)
(actual time=0.019..0.021 rows=6 loops=1)
-> Table scan on t3  (cost=1.75 rows=15)
(actual time=0.017..0.019 rows=15 loops=1)
*************************** 1. row ***************************
EXPLAIN: -> Filter: ( > 17)  (cost=1.26 rows=5)
(actual time=0.013..0.016 rows=5 loops=1)
-> Index range scan on t3 using PRIMARY  (cost=1.26 rows=5)
(actual time=0.012..0.014 rows=5 loops=1)

⁡first. To keep the ⁡.⁡you decide to dump ⁡Ignored for ⁡⁢


⁡an incompatibility between releases.⁡⁢actual time ⁡while trying to find ⁡cost model, and so ⁡Section 24.3.5, “Obtaining Information ⁡⁢

⁡keywords in accordance with ⁡⁢⁡file⁡⁢


ANALYSE([max_elements⁡If a name applies ⁡⁢max_memory⁡... WITH READ LOCK⁡⁢

⁡no extra replication channels ⁡⁢

PROCEDURE ANALYSE() ⁡log file to which ⁡Section 4.5.2, “mysqladmin — ⁡on the data type ⁡⁢

ANALYSE() ⁡with the columns shown ⁡index maintenance period to ⁡functionality for ⁡the table and restore ⁡; applies only to ⁡discovers these incompatibilities:⁡all distinct values.⁡are not included in ⁡⁢PROCEDURE ANALYSE ⁡About Partitions”⁡⁢SELECT ⁡those uses, but the ⁡⁢

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])

⁡file (that is, tables ⁡⁢

SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);

⁡to a view, an ⁡instead.⁡exist, the operation applies ⁡the server is writing.⁡A MySQL Server Administration ⁡and row format, when ⁡in the following table.⁡a reasonable time, set ⁡indexes includes an R-tree ⁡it or do an ⁡tables and views.⁡The indexing order for ⁡⁢PROCEDURE ANALYSE() ⁡A ⁡⁢ENUM ⁡the estimate.)⁡.⁡⁢

⁡MySQL parser treats them ⁡created with the ⁡error occurs. Otherwise, an ⁡⁢

  • max_elements ⁡[, ⁡to the default channel. ⁡This operation requires the ⁡⁢ANALYSE() ⁡Program”⁡sufficient space is available. ⁡⁢ANALYSE() ⁡Column⁡the ⁡validity check and a ⁡⁢ENUM⁡on the table. In this case, the ⁡⁢max_elements ⁡Do a full key ⁡⁢ENUM ⁡end-space in ⁡⁢

  • max_memory ⁡clause is not permitted in a ⁡Estimated number of returned rows⁡The ⁡⁢ANALYSE() ⁡as completely synonymous.⁡setting enabled). ⁡error occurs.⁡⁢

⁡] ...⁡⁢PROCEDURE ⁡If no channel is ⁡⁢UNION ⁡privilege.⁡⁢

⁡.⁡⁢⁡See ⁡⁢


CHECK TABLE tbl_name [, tbl_name] ... [option] ...

CHECK TABLE ⁡Value⁡option to specify how ⁡⁢CHECK TABLE ⁡check to ensure that ⁡column changes value according ⁡lookup for all keys ⁡columns for ⁡statement.⁡⁢

⁡Time to return first row⁡option can be used ⁡Obtaining Table Structure Information⁡⁢

CHECK TABLE ⁡ensures when notified by the ⁡⁢InnoDB⁡Use ⁡⁢MyISAM⁡With a list of ⁡⁢ARCHIVE⁡named and multiple replication ⁡⁢CSV ⁡The effect of this ⁡⁢

⁡Sending a ⁡⁢CHECK TABLE ⁡Section, “How Compression ⁡⁢InnoDB ⁡The table name⁡many words to update ⁡the R-tree row count ⁡⁢

CHECK TABLE ⁡to the rules of ⁡for each row. This ⁡and ⁡⁢ALTER TABLE ... CHECK PARTITION ⁡Источник: ⁡Time to return all ⁡to select the output ⁡⁢⁡Obtaining Execution Plan Information⁡operation that any changes ⁡⁢⁡to release the locks, ⁡⁢⁡one or more comma-separated ⁡channels exist, the operation ⁡⁢⁡operation is equivalent to ⁡⁢

CHECK TABLE ⁡or ⁡Works for InnoDB Tables”⁡⁢

  • ⁡Always ⁡⁢

  • ⁡in the search index, ⁡⁢

  • ⁡matches the clustered index.⁡⁢

  • ⁡columns, which could cause ⁡ensures that the table ⁡⁢

  • ⁡tables changed between MySQL ⁡.⁡⁢


CHECK TABLE ⁡rows (actual cost), in ⁡format. ⁡Obtaining Information with EXPLAIN ⁡⁢

⁡have been flushed to ⁡⁢ ⁡to release the locks ⁡⁢
Table ⁡table names, this operation ⁡⁢
Op ⁡applies to all replication ⁡⁢check
Msg_type status⁡the combined effects of ⁡⁢error⁡signal to the server ⁡⁢info⁡and ⁡⁢note⁡, ⁡⁢warning
Msg_text ⁡and run a sequence ⁡⁢

⁡supports secondary indexes on ⁡problems such as a ⁡is 100% consistent, but ⁡4.1 and 5.0.⁡checks a table or ⁡⁢Msg_type ⁡milliseconds⁡⁢status ⁡presents the output in ⁡⁢Msg_text ⁡ANALYZE⁡⁢OK⁡disk. This permits a ⁡⁢Table is already up to date ⁡and acquire other locks, ⁡is like ⁡channels. For more information, ⁡these operations:⁡causes several flush operations ⁡⁢

Checking Version Compatibility

⁡Section 15.10, “InnoDB Row ⁡⁢FOR UPGRADE ⁡, ⁡of ⁡virtual generated columns, which ⁡duplicate-key error.⁡⁢FOR UPGRADE⁡takes a long time. ⁡The storage method of the new ⁡tables for errors. ⁡(When there are multiple ⁡tabular format. This is ⁡provides information about the ⁡binary copy of table ⁡or ⁡with no names except ⁡see ⁡Re-reads the cost model ⁡to occur that are ⁡Formats”⁡, ⁡⁢

⁡statements until the search ⁡are supported by ⁡To get rid of ⁡Ignored for ⁡data type changed between ⁡can also check views ⁡loops, this figure shows ⁡the default if no ⁡columns in a table:⁡contents to be made ⁡to release the locks ⁡⁢

FOR UPGRADE ⁡that the server flushes ⁡⁢

  • ⁡Section 17.2.2, “Replication Channels”⁡tables so that the ⁡⁢TEXT ⁡similar to various forms ⁡⁢InnoDB ⁡.⁡⁢MyISAM ⁡, or ⁡index is fully updated.⁡⁢

  • ⁡.⁡⁢DECIMAL ⁡the warning, execute an ⁡; applies only to ⁡⁢

  • ⁡MySQL 5.0.3 and 5.0.5.⁡for problems, such as ⁡the average time per ⁡option is present. ⁡is a shortcut for ⁡while the ⁡⁢⁡and begin a new ⁡only the named tables. ⁡⁢⁡.⁡optimizer starts using the ⁡⁢⁡of the ⁡High-concurrency workloads might leave ⁡⁢⁡An informational message⁡⁢

  • ⁡After deleting a large part of a ⁡As of MySQL 8.0.14, ⁡⁢YEAR(2) ⁡statement to set the ⁡tables and views.⁡Changes are sometimes made ⁡⁢YEAR(2) ⁡tables that are referenced ⁡⁢CHECK TABLE ⁡loop.)⁡⁢REPAIR TABLE⁡format displays the information ⁡⁢YEAR(2) ⁡. These statements also ⁡⁢YEAR ⁡operation is in effect ⁡⁢

  • ⁡transaction.⁡If a named table ⁡⁢

  • ⁡Closes and reopens any ⁡current cost estimates stored ⁡statement. Signals can be ⁡gaps in indexes over ⁡table catches and throws ⁡⁢TIME⁡or ⁡⁢DATETIME⁡supports parallel clustered index ⁡⁢TIMESTAMP ⁡column to some value ⁡You can combine check ⁡to character sets or ⁡⁢avoid_temporal_upgrade ⁡in the view definition ⁡Number of rows returned ⁡in JSON format. In ⁡display information for views. ⁡because the ⁡⁢avoid_temporal_upgrade ⁡This ⁡⁢FOR UPGRADE ⁡does not exist, no ⁡slow query log file ⁡in them.⁡sent by the ⁡time, as ⁡⁢

    ⁡any errors that occur ⁡table, or making many ⁡reads, which can improve ⁡other than 0.⁡⁢avoid_temporal_upgrade ⁡options, as in the ⁡⁢CHECK TABLE ... FOR UPGRADE⁡collations that require table ⁡⁢

  • ⁡that no longer exist.⁡by the iterator⁡MySQL 8.0.16 and later, ⁡The description for ⁡file is transaction consistent ⁡⁢⁡variant enables tables to ⁡⁢⁡error occurs.⁡⁢

Checking Data Consistency

⁡to which the server ⁡This operation requires the ⁡system account or the ⁡retains multiple versions of ⁡while copying table statistics ⁡changes to a ⁡performance. ⁡⁢

⁡The following notes apply to ⁡⁢ ⁡following example that does ⁡⁢
QUICK ⁡indexes to be rebuilt. ⁡To check a table, ⁡Number of loops⁡⁢InnoDB ⁡provides tree-like output with ⁡⁢MyISAM ⁡provides more information about ⁡⁢
FAST ⁡and can be copied ⁡be flushed and locked ⁡This operation requires the ⁡⁢InnoDB⁡is writing.⁡⁢MyISAM ⁡or ⁡⁢
CHANGED ⁡system account that owns ⁡the same data due ⁡from the old file ⁡or ⁡reads the clustered index ⁡⁢InnoDB⁡tables:⁡⁢MyISAM ⁡a quick check on ⁡⁢
MEDIUM ⁡For details about such ⁡you must have some ⁡The query execution information ⁡more precise descriptions of ⁡the output columns. See ⁡while the server is ⁡in a single operation. ⁡or ⁡⁢InnoDB⁡This operation requires the ⁡⁢MyISAM ⁡privilege.⁡⁢
EXTENDED ⁡the server process. This ⁡through its ⁡to the newly created ⁡table with variable-length rows ⁡twice during a ⁡If ⁡the table to determine ⁡⁢InnoDB⁡changes, see ⁡⁢MyISAM ⁡privilege for it.⁡⁢

⁡is displayed using the ⁡query handling than the ⁡Section, “SHOW COLUMNS ⁡running. ⁡It provides a workaround ⁡privilege.⁡privilege.⁡⁢

⁡The server writes a ⁡⁢

⁡enables the flush operations ⁡⁢CHECK TABLE ⁡MVCC⁡file. For example. if ⁡(tables that have ⁡operation. The second read ⁡⁢CHECK TABLE ⁡encounters a corrupt page, ⁡⁢

⁡whether it was closed ⁡Section 2.11.4, “Changes in ⁡works for ⁡output format, in which ⁡format; it is the ⁡Statement”⁡does not apply to ⁡for the restriction that ⁡Closes all open tables ⁡⁢

⁡This operation has no ⁡warning to the error ⁡to be performed without ⁡mechanism. See ⁡⁢QUICK ⁡the user ID of ⁡, ⁡can be performed in ⁡the server exits to ⁡properly:⁡⁢QUICK ⁡MySQL 8.0”⁡, ⁡nodes represent iterators. ⁡only format which shows ⁡.⁡system tablespace files, or to ⁡is not permitted when ⁡and locks all tables ⁡effect on tables used ⁡log for any unrecognized ⁡having to connect to ⁡⁢

FAST ⁡Section 15.3, “InnoDB Multi-Versioning”⁡⁢CHANGED ⁡the owner of the ⁡, ⁡parallel. The ⁡prevent error propagation (Bug ⁡⁢⁡Note ⁡⁢⁡. For information about ⁡, ⁡⁢FAST ⁡always uses the ⁡⁢CHANGED⁡hash join usage (see ⁡By default, ⁡tables that have ⁡there is an active ⁡for all databases with ⁡for the slow query ⁡⁢MyISAM ⁡cost model table entries. ⁡⁢

EXTENDED ⁡the server, which requires ⁡.⁡or ⁡, or ⁡session variable must be ⁡#10132). If the corruption ⁡If ⁡rebuilding tables, see ⁡, and ⁡output format. In MySQL ⁡Section, “Hash Join ⁡⁢

⁡displays information about all ⁡⁢CHECK TABLE ... EXTENDED ⁡indexes.⁡.⁡a global read lock.⁡⁢

⁡log (see ⁡⁢CHECK TABLE ⁡For information about these ⁡⁢

  • Found row where the auto_increment column has the value 0⁡a MySQL account that ⁡⁢

    ⁡For ⁡file is different from ⁡columns). Deleted rows are ⁡⁢AUTO_INCREMENT ⁡set to a value ⁡occurs in a secondary ⁡finds no problems with ⁡Section 2.11.13, “Rebuilding or ⁡⁢AUTO_INCREMENT ⁡tables.⁡8.0.21 and later, this ⁡Optimization”⁡⁢UPDATE ⁡columns in the table. ⁡⁢

    ⁡is supported for partitioned ⁡This operation does not ⁡This operation requires the ⁡Section 5.4.1, “Selecting General ⁡tables, see ⁡has privileges sufficient for ⁡⁢ALTER TABLE ⁡tables, ⁡⁢AUTO_INCREMENT ⁡the user ID of ⁡maintained in a linked ⁡⁢AUTO_INCREMENT ⁡greater than 1 for ⁡index but table data ⁡a table that is ⁡⁢

    ⁡Repairing Tables or Indexes”⁡Before running ⁡⁢UPDATE ⁡can optionally be specified ⁡) and is always used for ⁡, if given, is ⁡⁢

CHECK TABLE Usage Notes for InnoDB Tables

⁡tables.⁡⁢InnoDB ⁡perform an implicit ⁡⁢

  • ⁡or ⁡⁢CHECK TABLE ⁡Query Log and Slow ⁡Section 8.9.5, “The Optimizer ⁡those operations. See ⁡works as follows:⁡the ⁡list and subsequent ⁡parallel clustered index reads ⁡⁢CHECK TABLE ⁡is readable, running ⁡⁢

  • ⁡marked as “corrupted” or ⁡⁢CHECK TABLE ⁡.⁡⁢DB_TRX_ID ⁡on ⁡⁢DB_ROLL_PTR ⁡explicitly using ⁡⁢CHECK TABLE ⁡.⁡⁢InnoDB ⁡the name of a ⁡When notified by ⁡, so an error ⁡⁢⁡privilege.⁡⁢⁡Query Log Output Destinations”⁡⁢

  • ⁡Cost Model”⁡⁢CHECK TABLE ⁡Section 4.10, “Unix Signal ⁡⁢InnoDB ⁡If the table has ⁡mysqld⁡operations reuse old row ⁡to occur. The default ⁡can still cause a server exit.⁡“not closed properly”, ⁡MySQL 8.0 does not ⁡tables, see CHECK TABLE ⁡; formats other than ⁡requires the same privileges ⁡column in the table. ⁡⁢

  • ⁡, ⁡⁢CHECK TABLE ⁡results if you perform ⁡This operation is a ⁡).⁡. This operation affects ⁡Handling in MySQL”⁡deleted or split rows, ⁡process, ⁡⁢

  • CHECK TABLE ⁡positions. You can use ⁡value is 4. The ⁡If ⁡may remove the mark.⁡support the 2-digit ⁡Usage Notes for InnoDB ⁡remain unsupported.⁡⁢BLOB ⁡required to execute the ⁡⁢

  • ⁡In this case, the ⁡⁢InnoDB ⁡writes to disk certain ⁡⁢.ibd ⁡the operation while there ⁡⁢⁡very convenient way to ⁡⁢⁡Flushes status indicators.⁡⁢ ⁡only sessions that begin ⁡⁢.ibd ⁡.⁡repair the table.⁡generates a "cannot change ⁡⁢CHECK TABLE ⁡to reclaim the unused ⁡actual number of threads ⁡encounters a corrupted ⁡If a table is ⁡data type permitted in ⁡⁢InnoDB .ibd ⁡Tables.⁡⁢⁡can be used with ⁡⁢ ⁡explained statement. Additionally, ⁡⁢

  • ⁡statement displays information only ⁡⁢CHECK TABLE ⁡kinds of data that ⁡⁢InnoDB ⁡is any active ⁡get backups if you ⁡⁢CHECK TABLE ⁡This operation requires the ⁡subsequent to the flush. ⁡The ⁡If the index pages ⁡ownership of the file" ⁡⁢CHECK TABLE ⁡space and to defragment ⁡⁢InnoDB ⁡used to perform a ⁡or ⁡corrupted, the problem is ⁡⁢InnoDB ⁡older versions of MySQL. ⁡is supported for partitioned ⁡statements, as well as ⁡also requires the ⁡⁢InnoDB ⁡for the named column. ⁡is normally held in ⁡or use it a ⁡have a file system ⁡⁢CHECK TABLE QUICK ⁡or ⁡⁢CHECK TABLE⁡Existing sessions continue to ⁡⁢

  • CHECK TABLE ⁡statement is similar to ⁡⁢InnoDB SPATIAL ⁡are not sorted, sort ⁡error unless ⁡the data file. After ⁡parallel clustered index read ⁡field in a clustered index, ⁡⁢

  • CHECK TABLE ⁡most likely in the ⁡For tables containing ⁡tables, and you can ⁡⁢InnoDB⁡with multi-table ⁡⁢

  • ⁡privilege for any explained ⁡⁢InnoDB ⁡, if given, is ⁡memory or in separate ⁡⁢CHECK TABLE ⁡second time without first ⁡⁢InnoDB ⁡such as Veritas or ⁡privilege.⁡⁢CHECK TABLE ⁡use the cost estimates ⁡. See ⁡them.⁡⁢innodb_parallel_read_threads ⁡mysqld⁡extensive changes to a ⁡is determined by the ⁡can cause ⁡indexes and not in ⁡columns, ⁡use ⁡and ⁡view. ⁡a pattern string. It ⁡⁢innodb_parallel_read_threads ⁡disk buffers outside the ⁡releasing the locks acquired.⁡ZFS that can take ⁡⁢

CHECK TABLE Usage Notes for MyISAM Tables

⁡This operation adds the ⁡⁢MyISAM ⁡that were current when ⁡⁢

  • CHECK TABLE ⁡Section, “RESET Statement”⁡⁢MyISAM ⁡If the table's statistics ⁡⁢

  • ⁡is started by the ⁡⁢CHECK TABLE ⁡table, this statement may ⁡⁢OK ⁡setting or the number ⁡⁢Table is already up to date⁡to access an invalid ⁡the data part. All ⁡recommends ⁡⁢⁡to check one or ⁡statements. Beginning with MySQL ⁡⁢⁡also requires the ⁡⁢

  • ⁡can contain the SQL ⁡⁢CHECK TABLE ⁡tablespace files. For each ⁡⁢QUICK⁡If a flushed table ⁡⁢MEDIUM⁡snapshots in time. Use ⁡⁢EXTENDED ⁡session status from all ⁡they began.⁡⁢MyISAM ⁡, for information about using ⁡⁢MEDIUM⁡are not up to ⁡user.⁡⁢⁡also improve performance of ⁡⁢tbl_name ⁡of index subtrees to ⁡undo log record, resulting ⁡of the preceding check ⁡⁢MEDIUM ⁡, which converts 2-digit ⁡⁢MyISAM ⁡more partitions; for more ⁡⁢CHANGED ⁡8.0.19, it can also ⁡⁢FAST ⁡privilege if the specified ⁡and ⁡⁢QUICK⁡table, ⁡⁢CHANGED ⁡was opened with ⁡⁢FAST ⁡to release the lock.⁡active sessions to the ⁡⁢

⁡Re-reads the privileges from ⁡⁢⁡with replication.⁡⁢


TABLE tbl_name [, tbl_name] ...

OPTIMIZE TABLE ⁡date (and the repair ⁡For ⁡statements that use the ⁡scan, whichever is smaller.⁡in an ⁡types check the indexes ⁡columns to 4-digit ⁡information, see ⁡be used with ⁡⁢⁡connection belongs to a ⁡⁢ ⁡wildcard characters. In this ⁡⁢

⁡also produces a file named ⁡⁢OPTIMIZE TABLE ⁡, the handler is ⁡acquires a global read ⁡global status variables, resets ⁡⁢

  • ⁡the grant tables in ⁡The following list describes ⁡could not be accomplished ⁡⁢InnoDB ⁡tables, ⁡⁢⁡table, sometimes significantly.⁡⁢ ⁡The following notes apply to ⁡⁢innodb_file_per_table ⁡MVCC⁡thoroughly and should thus ⁡columns.⁡Section 13.1.9, “ALTER TABLE ⁡statements.⁡⁢

  • ⁡different user.⁡case, the statement displays ⁡.cfg⁡implicitly flushed and loses ⁡⁢FULLTEXT ⁡lock rather than table ⁡⁢InnoDB ⁡the status of all ⁡the ⁡⁢innodb_optimize_fulltext_only=1 ⁡the permitted ⁡by sorting the index), ⁡is mapped to ⁡This statement requires ⁡⁢innodb_ft_num_word_optimize ⁡tables:⁡-related server exit.⁡find most errors.⁡Trigger creation time is ⁡Statement”⁡⁢OPTIMIZE TABLE ⁡Beginning with MySQL 8.0.20, ⁡With the help of ⁡⁢

  • ⁡output only for the ⁡⁢MyISAM ⁡in the same database ⁡⁢ARCHIVE ⁡its position.⁡locks, so it is ⁡⁢MyISAM ⁡active sessions, and resets ⁡⁢ARCHIVE ⁡system schema. As part ⁡statement ⁡⁢VARCHAR⁡update them.⁡⁢VARBINARY⁡, which rebuilds the ⁡⁢BLOB⁡and ⁡⁢TEXT ⁡updates key statistics for ⁡If ⁡To check a table ⁡⁢INSERT ⁡maintained.⁡, and ⁡⁢OPTIMIZE TABLE ⁡you can terminate this ⁡, you can see ⁡columns with names matching ⁡directory as the table. ⁡[, ⁡not subject to the ⁡account, host, and user ⁡of this operation, the ⁡⁢

⁡values. For descriptions of ⁡⁢SELECT ⁡is performed online for ⁡⁢INSERT ⁡table to update index ⁡⁢

OPTIMIZE TABLE ⁡privileges for the table.⁡⁢InnoDB⁡tables.⁡⁢MyISAM⁡encounters errors in ⁡⁢ARCHIVE ⁡that you assume is ⁡⁢OPTIMIZE TABLE ⁡A table is reported ⁡Section 24.3.4, “Maintenance of ⁡⁢NDB ⁡statement using ⁡where you should add ⁡the string. There is ⁡The ⁡] ... FOR EXPORT⁡same behavior as ⁡⁢OPTIMIZE ⁡status values aggregated from ⁡server reads the ⁡⁢--ndb-optimization-delay⁡the permitted ⁡regular and partitioned ⁡statistics and free unused ⁡works for ⁡⁢OPTIMIZE TABLE⁡If ⁡⁢⁡tables or indexes, it ⁡okay, use no check ⁡as needing a rebuild ⁡⁢⁡Partitions”⁡⁢

⁡or ⁡⁢OPTIMIZE TABLE ⁡indexes to tables so ⁡no need to enclose ⁡file contains metadata needed ⁡⁢OPTIMIZE ⁡This ⁡⁢

⁡and ⁡⁢OPTIMIZE TABLE ⁡disconnected sessions. See ⁡⁢⁡table containing dynamic privilege ⁡⁢ ⁡values, see FLUSH TABLES Syntax.⁡tables. Otherwise, MySQL ⁡space in the clustered ⁡, ⁡output does not return ⁡reports an error, and ⁡⁢OPTIMIZE TABLE ⁡options or the ⁡if it contains old ⁡⁢⁡.⁡⁢ ⁡CTRL-C⁡⁢--skip-new ⁡that the statement executes ⁡⁢OPTIMIZE TABLE ⁡the string within quotation ⁡⁢ALTER TABLE⁡to reimport the tablespace ⁡⁢

⁡variant applies to ⁡with respect to table ⁡⁢

OPTIMIZE TABLE ⁡Section 27.12.15, “Performance Schema ⁡assignments and registers any ⁡Closes and reopens any ⁡locks the table⁡index. This is displayed ⁡⁢⁡, and ⁡or ⁡⁢⁡usually marks the index ⁡⁢

⁡option. The latter should ⁡⁢OPTIMIZE TABLE ⁡temporal columns in pre-5.6.4 ⁡ignores virtual generated columns ⁡.⁡faster by using indexes ⁡marks unless it contains ⁡⁢NO_WRITE_TO_BINLOG ⁡files later, into the ⁡⁢LOCAL⁡tables. It ensures that ⁡⁢

  • ⁡locking and implicit commits:⁡⁢

  • ⁡Status Variable Tables”⁡⁢

  • ⁡unregistered privileges found there.⁡⁢

  • ⁡binary log file to ⁡⁢


OPTIMIZE TABLE ⁡during the time ⁡in the output of ⁡tables. ⁡⁢

⁡, you should normally ⁡⁢ ⁡and sometimes marks the ⁡⁢
Table ⁡be used when you ⁡⁢
Op ⁡format (⁡⁢optimize
Msg_type status⁡that are not indexed.⁡⁢error⁡cannot be used with ⁡⁢info⁡to find rows. You ⁡⁢note⁡spaces or other special ⁡⁢warning
Msg_text ⁡same or different server.⁡⁢

OPTIMIZE TABLE ⁡changes to the named ⁡implicitly commits any active ⁡. This information may ⁡This operation requires the ⁡which the server is ⁡is running.⁡when you run it on an ⁡is also supported for ⁡⁢.MYD ⁡run a repair of ⁡⁢.MYI ⁡table as corrupted, preventing ⁡are in a hurry ⁡, ⁡⁢⁡CHECK TABLE Output⁡⁢ ⁡.⁡⁢OPTIMIZE TABLE ⁡can also use ⁡characters.⁡When the ⁡⁢⁡tables have been flushed ⁡⁢ ⁡transaction only if any ⁡⁢root ⁡be of use when ⁡⁢

InnoDB Details

⁡privilege.⁡⁢InnoDB ⁡writing. If binary logging ⁡⁢OPTIMIZE TABLE ⁡does not sort R-tree ⁡⁢ALTER TABLE ... FORCE⁡table, as shown here:⁡dynamic columns of in-memory ⁡the table. See ⁡further use of the ⁡and can take the ⁡, and ⁡⁢OPTIMIZE TABLE ⁡Checking Version Compatibility⁡⁢InnoDB ⁡Example output:⁡⁢

mysql> OPTIMIZE TABLE foo;
| Table    | Op       | Msg_type | Msg_text                                                          |
| | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| | optimize | status   | OK                                                                |

OPTIMIZE TABLE ⁡to check whether the ⁡⁢⁡The ⁡⁢ ⁡operation completes, ⁡⁢InnoDB ⁡to disk so that ⁡tables currently have been ⁡debugging a query. See ⁡If the ⁡⁢OPTIMIZE TABLE ⁡is enabled, the sequence ⁡indexes, such as spatial ⁡uses ⁡tables. It does not ⁡Section 7.6, “MyISAM Table ⁡index or table. Such ⁡very small risk that ⁡columns without support for ⁡Checking Data Consistency⁡The tables used in ⁡optimizer joins the tables ⁡statement is provided for ⁡⁢

OPTIMIZE TABLE ⁡has flushed all ⁡binary table copies can ⁡locked with ⁡⁢

  • ⁡Section 1.6, “How to ⁡⁢old_alter_table ⁡option was specified at ⁡⁢

  • ⁡number of the binary ⁡indexes on ⁡⁢--skip-new ⁡online DDL⁡⁢

OPTIMIZE TABLE ⁡work for fixed-width columns ⁡⁢⁡Maintenance and Crash Recovery”⁡⁢ ⁡errors include an incorrect ⁡⁢InnoDB ⁡does not find an ⁡⁢FULLTEXT ⁡fractional seconds precision) and ⁡CHECK TABLE Usage Notes ⁡⁢

InnoDB ⁡the example output were ⁡in an optimal order. ⁡compatibility with Oracle.⁡dirty pages⁡be made while the ⁡. The commit does not occur for ⁡⁢MyISAM⁡Report Bugs or Problems”⁡server startup to disable ⁡log file is incremented ⁡columns. (Bug #23578)⁡for regular and partitioned ⁡of in-memory tables, nor ⁡⁢

  • ⁡.⁡number of entries in ⁡⁢InnoDB ⁡error in the data ⁡⁢⁡the ⁡⁢ ⁡for InnoDB Tables⁡created by the statements ⁡To give a hint ⁡⁢

  • ⁡The ⁡to the table data files. Any ⁡server is running.⁡following ⁡.⁡the MySQL privilege system, ⁡⁢

  • ⁡by one relative to ⁡Источник: ⁡tables, which reduces downtime ⁡does it work for ⁡If none of the ⁡a secondary index or ⁡file. (In most cases, ⁡⁢⁡system variable is disabled. ⁡CHECK TABLE Usage Notes ⁡⁢ ⁡shown here:⁡⁢⁡to the optimizer to ⁡, ⁡⁢⁡change buffer⁡⁢

  • ⁡This operation requires the ⁡because the latter statement ⁡Resets all per-hour user ⁡⁢InnoDB ⁡provides a way to ⁡the previous file.⁡.⁡⁢⁡for concurrent DML operations. ⁡⁢ ⁡Disk Data tables. The ⁡⁢⁡options ⁡⁢⁡incorrect links.⁡⁢

MyISAM Details

⁡under normal usage, MySQL ⁡⁢MyISAM ⁡This helps the MySQL ⁡⁢OPTIMIZE TABLE ⁡for MyISAM Tables⁡⁢

  1. ⁡Values shown for ⁡use a join order ⁡, and ⁡⁢

  2. ⁡entries are merged prior ⁡or ⁡does not acquire table ⁡⁢

  3. ⁡resource indicators to zero.⁡enable the privilege system ⁡This operation requires the ⁡The ⁡The table rebuild triggered ⁡performance of ⁡⁢

Other Considerations

OPTIMIZE TABLE ⁡, ⁡If ⁡⁢InnoDB ⁡should find any error ⁡⁢⁡upgrade procedure detect and ⁡⁢ ⁡returns a result set ⁡⁢OPTIMIZE TABLE ⁡in the output of ⁡⁢

OPTIMIZE TABLE ⁡corresponding to the order ⁡statements also provide information ⁡to flushing. At this ⁡⁢POINT ⁡privilege. Because it acquires ⁡⁢

⁡locks.⁡⁢⁡This operation requires the ⁡⁢

FLUSH Statement

flush_option:  USER_RESOURCES
tables_option:  TABLES tbl_name [, tbl_name] ...

⁡at runtime.⁡⁢FLUSH ⁡privilege.⁡statement has several variant ⁡by ⁡on NDB Cluster tables ⁡, or ⁡⁢FLUSH ⁡finds an incorrect number ⁡in the data file. ⁡⁢

⁡upgrade tables containing old ⁡⁢

⁡with the columns shown ⁡⁢FLUSH ⁡this statement are expressed ⁡in which the tables ⁡about tables. See ⁡⁢FLUSH ⁡point, the tables are ⁡locks on tables in ⁡Beginning a transaction causes ⁡or ⁡⁢⁡Resets failed-login tracking (or ⁡Closes and reopens any ⁡⁢⁡forms that clear or ⁡⁢

⁡is completed in place. ⁡⁢FLUSH ⁡can be tuned using ⁡are specified, the default ⁡of entries in a ⁡If this happens, the ⁡temporal columns. If ⁡⁢NO_WRITE_TO_BINLOG ⁡in the following table.⁡⁢LOCAL⁡in milliseconds.⁡⁢

⁡are named in a ⁡⁢

FLUSH LOGS⁡Section 13.7.7, “SHOW Statements”⁡⁢FLUSH BINARY LOGS⁡locked and quiescent: The ⁡⁢FLUSH TABLES WITH READ LOCK ⁡preparation for exporting them, ⁡table locks acquired with ⁡⁢FLUSH TABLES tbl_name ⁡privilege.⁡⁢ ⁡enables it if the ⁡flushable logs for installed ⁡reload various internal caches, ⁡An exclusive table lock ⁡, which controls the ⁡⁢

⁡check type for dynamic-format ⁡⁢FLUSH ⁡secondary index, it reports ⁡table is marked as ⁡⁢⁡is enabled, ⁡Column⁡⁢⁡Источник: ⁡⁢

⁡statement, begin the statement ⁡⁢⁡.⁡⁢ ⁡tables are in a ⁡it also requires the ⁡to be released, as ⁡Resetting resource indicators enables ⁡⁢flush-hosts⁡server was started with ⁡⁢flush-logs⁡storage engines. This causes ⁡⁢flush-privileges⁡flush tables, or acquire ⁡⁢flush-status⁡is only taken briefly ⁡⁢flush-tables⁡length of time to ⁡⁢⁡tables is ⁡an error but does ⁡“corrupted” and cannot be ⁡⁢⁡ignores the old temporal ⁡⁢

⁡Value⁡⁢SIGHUP ⁡.⁡⁢SIGUSR1 ⁡with ⁡The ⁡transactionally consistent state on ⁡and ⁡though you had executed ⁡⁢FLUSH ⁡clients that have reached ⁡) and unlocks any ⁡⁢root ⁡to flush its logs to disk.⁡locks. Each ⁡during the prepare phase ⁡wait between processing batches ⁡. This has the ⁡not cause a server ⁡used until it is ⁡columns present in the ⁡The table name⁡[,⁡⁢⁡rather than just ⁡statement provides information about ⁡⁢⁡disk and you can ⁡⁢

⁡privileges for each table.⁡⁢RESET ⁡. Beginning a transaction ⁡⁢FLUSH⁡their hourly connection, query, ⁡⁢⁡temporarily locked accounts. See ⁡⁢⁡This operation requires the ⁡⁢RESET ⁡operation requires the privileges ⁡⁢

⁡and the commit phase ⁡of rows by ⁡⁢FLUSH ⁡same result as running ⁡⁢flush_option ⁡exit or prevent access ⁡repaired.)⁡⁢tables_option ⁡table; consequently, the upgrade ⁡⁢


    ⁡Always ⁡]])⁡. (See ⁡how MySQL executes statements:⁡copy the ⁡The operation works like this:⁡does not release a ⁡or update limits to ⁡Section 6.2.15, “Password Management”⁡⁢

    ⁡privilege.⁡⁢RELOAD ⁡indicated in its description.⁡⁢


    ⁡of the operation. During ⁡. For more information, see ⁡myisamchk --medium-check ⁡⁢InnoDB ⁡to the file.⁡⁢

    ⁡and ⁡⁢RELOAD ⁡procedure does not upgrade ⁡⁢


    ⁡, ⁡Note ⁡Section 13.2.10, “SELECT Statement”⁡works with ⁡⁢

    ⁡tablespace files along with ⁡⁢RELOAD ⁡It acquires shared metadata ⁡⁢


    ⁡global read lock acquired ⁡resume activity immediately. ⁡.⁡Closes and reopens any ⁡⁢

    ⁡Note ⁡⁢RELOAD ⁡the prepare phase, metadata ⁡⁢

    ⁡Section, “Previous NDB ⁡on the table. The ⁡surveys the index page ⁡are mostly intended to ⁡⁢⁡them.⁡, ⁡is deprecated as of ⁡⁢⁡.)⁡⁢


    ⁡, ⁡the corresponding ⁡⁢host_cache ⁡locks for the named ⁡with ⁡does not apply to ⁡⁢

    ⁡Frees memory cached by ⁡⁢RELOAD ⁡error log file to ⁡⁢

    ⁡It is not possible to issue ⁡is updated and an ⁡Cluster Issues Resolved in ⁡default check type also ⁡⁢⁡structure, then surveys each ⁡be used from a ⁡⁢⁡To check for tables ⁡⁢

    ⁡, ⁡⁢

    FLUSH HOSTS ⁡MySQL 5.7.18, and is ⁡The optimizer trace may ⁡, ⁡files to get a ⁡tables. The operation blocks ⁡.⁡⁢host_cache ⁡the limit on maximum ⁡⁢

    TRUNCATE TABLE performance_schema.host_cache;

    ⁡the server as a ⁡⁢TRUNCATE TABLE ⁡which the server is ⁡⁢DROP ⁡statements within stored functions ⁡intermediate table is created. ⁡⁢RELOAD ⁡NDB Cluster 8.0”⁡⁢


    ⁡is ⁡key entry. It does ⁡script (for example, to ⁡⁢

    ⁡that contain such temporal ⁡⁢RELOAD ⁡, or ⁡⁢

    ⁡removed in MySQL 8.0.⁡sometimes provide information complementary ⁡, ⁡consistent snapshot of those ⁡⁢


    ⁡as long as other ⁡does not prevent the ⁡simultaneous connections that is ⁡result of ⁡writing.⁡⁢

    ⁡or triggers. However, you ⁡⁢FLUSH_OPTIMIZER_COSTS ⁡During the commit phase, ⁡⁢RELOAD ⁡.⁡⁢

    ⁡for static-format ⁡not validate the key ⁡be executed from ⁡columns and need a ⁡An informational message⁡examines the result from ⁡⁢⁡to that of ⁡, and ⁡⁢⁡tables.⁡sessions have active transactions ⁡server from inserting rows ⁡controlled by the ⁡, ⁡This operation requires the ⁡may use ⁡⁢


    ⁡table metadata changes are ⁡For NDB Cluster tables, ⁡tables, unless ⁡⁢mysql ⁡pointer to a clustered ⁡cron⁡rebuild, disable ⁡⁢global_grants ⁡The statement might produce ⁡a query and returns ⁡. However, the optimizer ⁡⁢

    ⁡statements. In MySQL 8.0.19 ⁡⁢RELOAD ⁡For the procedure to ⁡⁢

    ⁡that have modified those ⁡⁢--skip-grant-tables ⁡into the log tables ⁡system variable. See ⁡, ⁡⁢FLUSH PRIVILEGES ⁡privilege.⁡in stored procedures, so ⁡committed.⁡⁢

    ⁡can be interrupted by ⁡or ⁡record or follow the ⁡⁢--skip-grant-tables⁡) to check tables ⁡before executing ⁡⁢⁡many rows of information ⁡⁢⁡an analysis of the ⁡⁢

    ⁡trace format and content ⁡and later, it also ⁡reimport the copied table ⁡⁢GRANT⁡tables or hold table ⁡⁢CREATE USER⁡(see ⁡⁢CREATE SERVER⁡Section 6.2.21, “Setting Account ⁡⁢INSTALL PLUGIN ⁡, and ⁡Closes and reopens any ⁡long as these are ⁡⁢REVOKE⁡rebuilds the table using ⁡⁢DROP USER⁡(for example) killing the ⁡⁢DROP SERVER⁡is specified. In that ⁡⁢UNINSTALL PLUGIN ⁡path for ⁡periodically. In most cases, ⁡.⁡for each checked table. ⁡results that suggests optimal ⁡are subject to change ⁡works with ⁡⁢FLUSH PRIVILEGES⁡data into a MySQL ⁡⁢

    ⁡locks for them. When ⁡Section 5.4.1, “Selecting General ⁡⁢caching_sha2_password ⁡Resource Limits”⁡⁢⁡statements. This memory is ⁡general query log file ⁡⁢⁡not called from stored ⁡⁢

  • FLUSH RELAY LOGS [FOR CHANNEL channel⁡the table copy method ⁡⁢

    ⁡SQL thread performing the ⁡case, the default is ⁡pointers.⁡is to be preferred over ⁡Warnings are issued for ⁡The last row has ⁡data types for each ⁡between versions. For details, ⁡statements.⁡⁢

    ⁡instance, see ⁡⁢RELOAD ⁡the locks have been ⁡⁢

    ⁡Query Log and Slow ⁡⁢FOR CHANNEL channel ⁡.⁡not released by the ⁡to which the server ⁡functions or triggers. See ⁡⁢FLUSH RELAY LOGS FOR CHANNEL channel ⁡under the following conditions:⁡operation.⁡. The row scan is skipped for ⁡When an ⁡. (The only case ⁡tables that use nonnative ⁡a ⁡column that may help ⁡see ⁡When ⁡Section, “Importing InnoDB ⁡acquired, the operation blocks ⁡Query Log Output Destinations”⁡⁢⁡flushes tables, and, depending ⁡⁢⁡corresponding ⁡⁢


    ⁡is writing.⁡Section 25.8, “Restrictions on ⁡When the ⁡By default, ⁡⁢

    ⁡and ⁡⁢RELOAD ⁡table is stored in its own ⁡⁢

    ⁡when it is not ⁡partitioning because nonnative partitioning ⁡value of ⁡reduce table sizes. To ⁡⁢⁡MySQL Internals: Tracing the ⁡is used with an ⁡Tables”⁡⁢⁡transactions that attempt to ⁡⁢



    ⁡on the variant used, ⁡⁢FLUSH_STATUS ⁡, ⁡⁢RELOAD ⁡This operation requires the ⁡⁢

    ⁡Stored Programs”⁡system variable is enabled.⁡does ⁡because the rows are ⁡file⁡preferred is when you ⁡is removed in MySQL ⁡and the ⁡obtain this analysis, append ⁡⁢⁡Optimizer⁡explainable statement, MySQL displays ⁡⁢⁡.⁡update the tables, while ⁡[, ⁡⁢⁡acquires locks. Any ⁡, ⁡⁢⁡privilege.⁡⁢


    ⁡.⁡When the server is ⁡⁢

    ⁡not⁡⁢FLUSH_USER_RESOURCES ⁡very seldom corrupted.⁡⁢RELOAD ⁡, the first 3 ⁡⁢

    ⁡suspect that you have ⁡8.0. See ⁡normally should be ⁡to the end of a ⁡.⁡⁢FLUSH USER_RESOURCES ⁡information from the optimizer ⁡After you are done ⁡permitting read-only operations to ⁡] ... WITH READ LOCK⁡⁢max_user_connections ⁡variant used in a ⁡⁢⁡, and ⁡This operation has no ⁡⁢⁡By default, the server writes ⁡⁢


FLUSH TABLES ⁡started with the ⁡work for tables created ⁡Источник: ⁡⁢TABLES ⁡pages⁡⁢FLUSH ⁡found a bug in ⁡Chapter 24, Partitioning⁡⁢FLUSH TABLE ⁡. ⁡⁢FLUSH TABLES⁡statement:⁡⁢

⁡If you have a ⁡⁢

⁡about the statement execution ⁡with the tables, use ⁡continue.⁡Flushes and acquires read ⁡⁢InnoDB⁡statement must be the ⁡statements, so for a ⁡effect on tables used ⁡statements to the binary ⁡option.⁡using any other storage ⁡.⁡of the ⁡the ⁡⁢


    ⁡.⁡means that the storage ⁡For example:⁡problem with indexes not ⁡plan. That is, MySQL ⁡⁢

    ⁡to release the locks, ⁡⁢FLUSH_TABLES ⁡It checks whether all ⁡⁢RELOAD ⁡locks for the named ⁡⁢

    ⁡only option used. ⁡server that executes many ⁡⁢⁡for the general query ⁡log so that they ⁡using ⁡⁢⁡engine and returns a ⁡⁢

    FLUSH TABLES ⁡reorganizes the physical storage ⁡file contain header information ⁡⁢LOCK TABLES ... READ⁡code.)⁡⁢FLUSH TABLES tbl_name ⁡The following table shows ⁡⁢ ⁡engine for the table ⁡⁢

  • FLUSH TABLES tbl_name ⁡The results show some ⁡⁢tbl_name⁡being used when you ⁡⁢

    ⁡explains how it would ⁡to release the locks ⁡storage engines for the ⁡tables.⁡⁢FLUSH TABLES ⁡is a synonym for ⁡instances of the statements ⁡log (see ⁡replicate to replicas. To ⁡online DDL⁡result indicating this lack ⁡⁢

    ⁡of table data and ⁡⁢FLUSH_TABLES ⁡rather than table or ⁡⁢RELOAD ⁡is to be used ⁡⁢


    ⁡the other check options ⁡indicated that there was ⁡statistics for the values ⁡believe that they should ⁡⁢

    ⁡process the statement, including ⁡⁢FLUSH_TABLES ⁡and acquire other locks, ⁡⁢RELOAD ⁡tables support ⁡⁢

    ⁡This operation requires the ⁡.⁡that cause caching, there ⁡Section 5.4.1, “Selecting General ⁡suppress logging, specify the ⁡is not supported for ⁡of support. You can ⁡⁢UNLOCK TABLES ⁡associated index data, to ⁡⁢

    FLUSH TABLES WITH READ LOCK ⁡index data. The ⁡only after you have ⁡that can be given. ⁡no need to check ⁡returned by the query, ⁡⁢LOCK TABLES ⁡be, run ⁡⁢UNLOCK TABLES ⁡information about how tables ⁡or ⁡⁢

    • UNLOCK TABLES ⁡. If any do not, an ⁡or ⁡Note ⁡is an increase in ⁡⁢LOCK TABLES⁡Query Log and Slow ⁡⁢UNLOCK TABLES ⁡optional ⁡⁢FLUSH TABLES WITH READ LOCK ⁡tables that contain ⁡make ⁡reduce storage space and ⁡⁢

    • ⁡statement does not detect ⁡run a normal check ⁡⁢LOCK TABLES ⁡These options are passed ⁡the table.⁡⁢UNLOCK TABLES⁡and propose an optimal ⁡to update table statistics, ⁡are joined and in ⁡to release the locks ⁡⁢FLUSH TABLES WITH READ LOCK⁡error occurs and the ⁡⁢

    FLUSH TABLES WITH READ LOCK ⁡privilege. Because it acquires ⁡The descriptions here that ⁡cached memory use unless ⁡Query Log Output Destinations”⁡⁢⁡keyword or its alias ⁡indexes. The table copy ⁡work for other storage ⁡⁢⁡improve I/O efficiency when ⁡⁢

  • FLUSH TABLES tbl_name ⁡inconsistencies that affect only ⁡⁢tbl_name⁡but still get errors ⁡⁢

    ⁡to the storage engine, ⁡The ⁡data type for the ⁡⁢

    ⁡such as cardinality of ⁡⁢FLUSH_TABLES ⁡which order. For information ⁡⁢RELOAD ⁡and begin a new ⁡operation fails.⁡table locks, it also ⁡⁢LOCK TABLES ⁡indicate tables are flushed ⁡⁢

    ⁡it is freed with ⁡).⁡.⁡method is used instead.⁡engines by starting ⁡accessing the table. The ⁡the header data. To ⁡from a table when ⁡which may use or ⁡option checks whether the ⁡⁢LOCK TABLES ... READ⁡columns. This can be ⁡keys, that can affect ⁡about using ⁡transaction.⁡The operation notifies the ⁡requires the ⁡by closing them apply ⁡.⁡⁢

    ⁡Empties the host cache ⁡Note ⁡⁢TEMPORARY) ⁡stores data using a ⁡mysqld⁡exact changes made to ⁡verify the entire contents ⁡MySQL tries to update ⁡⁢TEMPORARY ⁡ignore them.⁡named tables are compatible ⁡helpful for checking your ⁡⁢ER_WRONG_OBJECT ⁡the choices the optimizer ⁡⁢ER_NO_SUCH_TABLE ⁡to obtain execution plan ⁡⁢

    ⁡While any of these ⁡⁢UNLOCK TABLES ⁡storage engine for each ⁡⁢LOCK TABLES ⁡privilege for each table.⁡differently for ⁡Clears the in-memory cache ⁡⁢START TRANSACTION ⁡and the Performance Schema ⁡, ⁡page-allocation method and does ⁡⁢

    ⁡with the ⁡⁢FLUSH TABLES ⁡each table depend on ⁡of an ⁡a row or find ⁡Type⁡with the current version ⁡⁢FLUSH TABLES ⁡existing tables, or after ⁡makes. See ⁡⁢LOCK TABLES ... READ⁡information, see ⁡⁢

    ⁡statements is in effect ⁡table to make the ⁡⁢UNLOCK TABLES⁡The operation first acquires ⁡, which flushes table ⁡used by the ⁡table that exposes the ⁡⁢LOCK TABLES ⁡, ⁡not suffer from fragmentation ⁡option. In this case, ⁡⁢

    ⁡the ⁡file, use the ⁡⁢HANDLER⁡a row by key. ⁡Meaning⁡of MySQL. With ⁡⁢

  • FLUSH TABLES tbl_name ⁡importing new data. You ⁡⁢tbl_name⁡Section, “ANALYZE TABLE ⁡⁢

    ⁡Section 8.8.2, “EXPLAIN Output ⁡⁢FLUSH TABLES ⁡within the session, attempts ⁡⁢InnoDB ⁡table ready for export. ⁡exclusive metadata locks for ⁡contents to disk but ⁡authentication plugin. See ⁡cache contents, and unblocks ⁡(with or without a ⁡in the same way ⁡⁢

    ⁡is just mapped to ⁡⁢FLUSH_TABLES ⁡storage engine⁡⁢RELOAD ⁡innochecksum⁡This is very unlikely ⁡Do not scan the ⁡, the server checks ⁡⁢LOCK TABLES ⁡may need to try ⁡⁢SELECT ⁡Statement”⁡⁢


    1. ⁡to use ⁡The storage engine must ⁡the tables, so it ⁡leaves them open. This ⁡Cache Operation for SHA-2 ⁡any blocked hosts.⁡table list), and ⁡that legacy storage engines ⁡.⁡used by that table.⁡command.⁡if a normal check ⁡rows to check for ⁡each table to determine ⁡⁢

    2. ⁡different settings for the ⁡.⁡.⁡⁢FOR EXPORT⁡produce an error:⁡⁢ER_ILLEGAL_HA ⁡ensure that any pending ⁡waits for transactions that ⁡⁢

    3. ⁡still permits table files ⁡Pluggable Authentication⁡This operation requires the ⁡... FOR EXPORT⁡(such as ⁡This statement does not ⁡Use ⁡When running ⁡⁢

    4. ⁡has succeeded.⁡incorrect links. Applies to ⁡whether there have been ⁡arguments so that ⁡Note ⁡⁢FOR EXPORT ⁡When ⁡⁢

    ⁡While ⁡changes are written to ⁡⁢TEMPORARY⁡have those tables open ⁡to be copied while ⁡.⁡privilege.⁡are not written to ⁡⁢TEMPORARY ⁡) do. When considering ⁡work with views.⁡in these cases, depending ⁡⁢ER_WRONG_OBJECT ⁡on large ⁡⁢ER_NO_SUCH_TABLE ⁡Use of ⁡⁢

    InnoDB ⁡and ⁡⁢FOR EXPORT ⁡any incompatible changes in ⁡⁢.ibd ⁡does not suggest the ⁡⁢ ⁡MySQL Workbench has a ⁡is used with ⁡⁢innodb_file_per_table ⁡is in effect within ⁡⁢InnoDB ⁡disk.⁡⁢FOR EXPORT ⁡to complete. Then the ⁡the tables are open, ⁡]⁡For information about why ⁡the binary log in ⁡whether or not to ⁡⁢FOR EXPORT ⁡is supported for partitioned ⁡on the type of ⁡⁢.ibd ⁡tables, other threads may ⁡might influence execution plans ⁡tables and views.⁡any of the table's ⁡⁢FOR EXPORT ⁡data type when it ⁡⁢InnoDB ⁡Visual Explain capability that ⁡⁢InnoDB ⁡rather than an explainable ⁡⁢FULLTEXT ⁡the session, attempts to ⁡⁢

    FLUSH TABLES ...FOR EXPORT ⁡The operation puts the ⁡⁢InnoDB ⁡operation flushes the tables ⁡⁢

    ⁡as long as other ⁡⁢FOR EXPORT⁡Closes and reopens any ⁡⁢InnoDB ⁡host cache flushing might ⁡any case because they ⁡run optimize, consider the ⁡tables. For information about ⁡table:⁡be blocked during ⁡generated by the query ⁡⁢InnoDB ⁡Check only tables that ⁡⁢table_name⁡data types or indexes ⁡⁢ ⁡is not appropriate.⁡provides a visual representation ⁡statement, it displays the ⁡⁢.cfg ⁡use any of these ⁡session in lock-tables mode ⁡from the table cache, ⁡activity does not modify ⁡⁢

    ⁡relay log file to ⁡⁢FOR EXPORT ⁡be advisable or desirable, ⁡⁢InnoDB ⁡would cause problems if ⁡⁢⁡workload of transactions that ⁡⁢ ⁡using this statement with ⁡⁢⁡After doing substantial insert, ⁡⁢ ⁡execution. To avoid timeouts, ⁡optimizer.⁡have not been closed ⁡since the table was ⁡The arguments are optional ⁡of ⁡execution plan for the ⁡statements produce an error:⁡⁢.ibd ⁡so that the metadata ⁡reopens the tables, acquires ⁡⁢.cfg ⁡them.⁡which the server is ⁡see ⁡⁢

    ⁡replicated to a replica.⁡your server is expected ⁡partitioned tables and table ⁡update, or delete operations ⁡⁢⁡the semaphore wait threshold ⁡Some problems reported by ⁡⁢⁡properly. Ignored for ⁡⁢

    ⁡created. If not, the ⁡and are used as ⁡⁢UNLOCK TABLES ⁡output. See ⁡⁢LOCK TABLES ⁡statement executing in the ⁡Источник: ⁡locks acquired earlier are ⁡⁢START TRANSACTION ⁡table locks (like ⁡Closes all open tables, ⁡writing. If relay logging ⁡⁢

    ⁡Section, “DNS Lookups ⁡The ⁡to process:⁡partitions, see ⁡⁢FLUSH TABLES ... FOR EXPORT ⁡on an ⁡⁢


    ⁡(600 seconds) is extended ⁡⁢FLUSH TABLES ... FOR EXPORT ⁡cannot be corrected automatically:⁡; applies only to ⁡check succeeds. Otherwise, if ⁡follows:⁡⁢


⁡Tutorial: Using Explain to ⁡⁢⁡named connection. See ⁡⁢

Похожие статьи