A tree data structure that is popular for use in database indexes. The structure is kept sorted at all times, enabling fast lookup for exact matches (equals operator) and ranges (for example, greater than, less than, and BETWEEN operators). This type of index is available for most storage engines, such as InnoDB and MyISAM.

Because B-tree nodes can have many children, a B-tree is not the same as a binary tree, which is limited to 2 children per node.

Contrast with hash index, which is only available in the MEMORY storage engine. The MEMORY storage engine can also use B-tree indexes, and you should choose B-tree indexes for MEMORY tables if some queries use range operators.

See Also .

Identifiers within MySQL SQL statements must be quoted using the backtick character (`) if they contain special characters or reserved words. For example, to refer to a table namedFOO#BAR or a column named SELECT, you would specify the identifiers as `FOO#BAR` and `SELECT`. Since the backticks provide an extra level of safety, they are used extensively in program-generated SQL statements, where the identifier names might not be known in advance.

Many other database systems use double quotation marks (") around such special names. For portability, you can enable ANSI_QUOTES mode in MySQL and use double quotation marks instead of backticks to qualify identifier names.

See Also .

The process of copying some or all table data and metadata from a MySQL instance, for safekeeping. Can also refer to the set of copied files. This is a crucial task for DBAs. The reverse of this process is the restore operation.

With MySQL, physical backups are performed by the MySQL Enterprise Backup product, and logical backups are performed by the mysqldump command. These techniques have different characteristics in terms of size and representation of the backup data, and speed (especially speed of the restore operation).

Backups are further classified as hot, warm, or cold depending on how much they interfere with normal database operation. (Hot backups have the least interference, cold backups the most.)

See Also , , , , , , .

The code name for an InnoDB file format that supports compression for table data. This file format was first introduced in the InnoDB Plugin. It supports the compressed row format that enables InnoDB table compression, and the dynamic row format that improves the storage layout for BLOB and large text columns. You can select it through the option.

Because the InnoDB system tablespace is stored in the original Antelope file format, to use the Barracuda file format you must also enable the file-per-table setting, which puts newly created tables in their own tablespaces separate from the system tablespace.

The MySQL Enterprise Backup product version 3.5 and above supports backing up tablespaces that use the Barracuda file format.

See Also , , , , , , , , , .

An early stage in the life of a software product, when it is available only for evaluation, typically without a definite release number or a number less than 1. InnoDB does not use the beta designation, preferring an early adopter phase that can extend over several point releases, leading to a GA release.

See Also , .

A file containing a record of all statements that attempt to change table data. These statements can be replayed to bring slave servers up to date in a replication scenario, or to bring a database up to date after restoring table data from a backup. The binary logging feature can be turned on and off, although Oracle recommends always enabling it if you use replication or perform backups.

You can examine the contents of the binary log, or replay those statements during replication or recovery, by using the  command. For full information about the binary log, see . For MySQL configuration options related to the binary log, see .

For the MySQL Enterprise Backup product, the file name of the binary log and the current position within the file are important details. To record this information for the master server when taking a backup in a replication context, you can specify the --slave-info option.

Prior to MySQL 5.0, a similar capability was available, known as the update log. In MySQL 5.0 and higher, the binary log replaces the update log.

See Also , , .

An informal name for the binary log file. For example, you might see this abbreviation used in e-mail messages or forum discussions.

See Also .

A special mode of full-text search enabled by the WITH QUERY EXPANSION clause. It performs the search twice, where the search phrase for the second search is the original search phrase concatenated with the few most highly relevant documents from the first search. This technique is mainly applicable for short search phrases, perhaps only a single word. It can uncover relevant matches where the precise search term does not occur in the document.

See Also .

A portion of a system that is constrained in size or capacity, that has the effect of limiting overall throughput. For example, a memory area might be smaller than necessary; access to a single required resource might prevent multiple CPU cores from running simultaneously; or waiting for disk I/O to complete might prevent the CPU from running at full capacity. Removing bottlenecks tends to improve concurrency. For example, the ability to have multiple InnoDB buffer pool instances reduces contention when multiple sessions read from and write to the buffer pool simultaneously.

See Also , .

A shutdown operation immediately followed by a restart. Ideally with a relatively short warmup period so that performance and throughput quickly return to a high level.

See Also .

A mechanism for managing different-sized pages in the InnoDB buffer pool.

See Also , , .

A memory or disk area used for temporary storage. Data is buffered in memory so that it can be written to disk efficiently, with a few large I/O operations rather than many small ones. Data is buffered on disk for greater reliability, so that it can be recovered even when a crash or other failure occurs at the worst possible time. The main types of buffers used by InnoDB are the buffer pool, the doublewrite buffer, and the insert buffer.

See Also , , , .

The memory area that holds cached InnoDB data for both tables and indexes. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm. On systems with large memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances.

Several InnoDB status variables, information_schema tables, and performance_schema tables help to monitor the internal workings of the buffer pool. Starting in MySQL 5.6, you can also dump and restore the contents of the buffer pool, either automatically during shutdown and restart, or manually at any time, through a set of InnoDB configuration variables such as  and .

See Also , , , .

Any of the multiple regions into which the buffer pool can be divided, controlled by the  configuration option. The total memory size specified by the  is divided among all the instances. Typically, multiple buffer pool instances are appropriate for systems devoting multiple gigabytes to the InnoDB buffer pool, with each instance 1 gigabyte or larger. On systems loading or looking up large amounts of data in the buffer pool from many concurrent sessions, having multiple instances reduces the contention for exclusive access to the data structures that manage the buffer pool.

See Also .

The built-in InnoDB storage engine within MySQL is the original form of distribution for the storage engine. Contrast with the InnoDB Plugin. Starting with MySQL 5.5, the InnoDB Plugin is merged back into the MySQL code base as the built-in InnoDB storage engine (known as InnoDB 1.1).

This distinction is important mainly in MySQL 5.1, where a feature or bug fix might apply to the InnoDB Plugin but not the built-in InnoDB, or vice versa.

See Also , .

The relationships and sequences of actions that form the basis of business software, used to run a commercial company. Sometimes these rules are dictated by law, other times by company policy. Careful planning ensures that the relationships encoded and enforced by the database, and the actions performed through application logic, accurately reflect the real policies of the company and can handle real-life situations.

For example, an employee leaving a company might trigger a sequence of actions from the human resources department. The human resources database might also need the flexibility to represent data about a person who has been hired, but not yet started work. Closing an account at an online service might result in data being removed from a database, or the data might be moved or flagged so that it could be recovered if the account is re-opened. A company might establish policies regarding salary maximums, minimums, and adjustments, in addition to basic sanity checks such as the salary not being a negative number. A retail database might not allow a purchase with the same serial number to be returned more than once, or might not allow credit card purchases above a certain value, while a database used to detect fraud might allow these kinds of things.

See Also .

A metadata file used with the InnoDB transportable tablespace feature. It is produced by the command FLUSH TABLES ... FOR EXPORT, puts one or more tables in a consistent state that can be copied to another server. The .cfg file is copied along with the corresponding .ibd file, and used to adjust the internal values of the .ibd file, such as the space ID, during the ALTER TABLE ... IMPORT TABLESPACE step.

See Also , , .

The general term for any memory area that stores copies of data for frequent or high-speed retrieval. In InnoDB, the primary kind of cache structure is the buffer pool.

See Also , .

The number of different values in a table column. When queries refer to columns that have an associated index, the cardinality of each column influences which access method is most efficient. For example, for a column with a unique constraint, the number of different values is equal to the number of rows in the table. If a table has a million rows but only 10 different values for a particular column, each value occurs (on average) 100,000 times. A query such as SELECT c1 FROM t1 WHERE c1 = 50; thus might return 1 row or a huge number of rows, and the database server might process the query differently depending on the cardinality of c1.

If the values in a column have a very uneven distribution, the cardinality might not be a good way to determine the best query plan. For example, SELECT c1 FROM t1 WHERE c1 = x; might return 1 row when x=50 and a million rows when x=30. In such a case, you might need to use index hints to pass along advice about which lookup method is more efficient for a particular query.

Cardinality can also apply to the number of distinct values present in multiple columns, as in a composite index.

For InnoDB, the process of estimating cardinality for indexes is influenced by the  and the  configuration options. The estimated values are more stable when the persistent statistics feature is enabled (in MySQL 5.6 and higher).

See Also , , , , , , , .

A special data structure that records changes to pages in secondary indexes. These values could result from SQL , , or  statements (DML). The set of features involving the change buffer is known collectively as change buffering, consisting of insert buffering, delete buffering, and purge buffering.

Changes are only recorded in the change buffer when the relevant page from the secondary index is not in the buffer pool. When the relevant index page is brought into the buffer pool while associated changes are still in the change buffer, the changes for that page are applied in the buffer pool (merged) using the data from the change buffer. Periodically, the purgeoperation that runs during times when the system is mostly idle, or during a slow shutdown, writes the new index pages to disk. The purge operation can write the disk blocks for a series of index values more efficiently than if each value were written to disk immediately.

Physically, the change buffer is part of the system tablespace, so that the index changes remain buffered across database restarts. The changes are only applied (merged) when the pages are brought into the buffer pool due to some other read operation.

The kinds and amount of data stored in the change buffer are governed by the  and  configuration options. To see information about the current data in the change buffer, issue the  command.

Formerly known as the insert buffer.

See Also , , , , , , , , , , , .

The general term for the features involving the change buffer, consisting of insert buffering, delete buffering, and purge buffering. Index changes resulting from SQL statements, which could normally involve random I/O operations, are held back and performed periodically by a background thread. This sequence of operations can write the disk blocks for a series of index values more efficiently than if each value were written to disk immediately. Controlled by the  and configuration options.

See Also , , , .

As changes are made to data pages that are cached in the buffer pool, those changes are written to the data files sometime later, a process known as flushing. The checkpoint is a record of the latest changes (represented by an LSN value) that have been successfully written to the data files.

See Also , , , , .

In InnoDB, a validation mechanism to detect corruption when a page in a tablespace is read from disk into the InnoDB buffer pool. This feature is turned on and off by the configuration option. In MySQL 5.6, you can enable a faster checksum algorithm by also specifying the configuration optioninnodb_checksum_algorithm=crc32.

The  command helps to diagnose corruption problems by testing the checksum values for a specified tablespace file while the MySQL server is shut down.

MySQL also uses checksums for replication purposes. For details, see the configuration options , , and.

See Also , , .

In a foreign key relationship, a child table is one whose rows refer (or point) to rows in another table with an identical value for a specific column. This is the table that contains theFOREIGN KEY ... REFERENCES clause and optionally ON UPDATE and ON DELETE clauses. The corresponding row in the parent table must exist before the row can be created in the child table. The values in the child table can prevent delete or update operations on the parent table, or can cause automatic deletion or updates in the child table, based on theON CASCADE option used when creating the foreign key.

See Also , .

A page in the InnoDB buffer pool where all changes made in memory have also been written (flushed) to the . The opposite of a dirty page.

See Also , , , , .

A shutdown that completes without errors and applies all changes to InnoDB tables before finishing, as opposed to a crash or a fast shutdown. Synonym for slow shutdown.

See Also , , , .

A type of program that sends requests to a server, and interprets or processes the results. The client software might run only some of the time (such as a mail or chat program), and might run interactively (such as the mysql command processor).

See Also , .

The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.

In the Oracle Database product, this type of table is known as an index-organized table.

See Also , , .

A backup taken while the database is shut down. For busy applications and web sites, this might not be practical, and you might prefer a warm backup or a hot backup.

See Also , , .

A data item within a row, whose storage and semantics are defined by a data type. Each table and index is largely defined by the set of columns it contains.

Each column has a cardinality value. A column can be the primary key for its table, or part of the primary key. A column can be subject to a unique constraint, a NOT NULL constraint, or both. Values in different columns, even across different tables, can be linked by a foreign key relationship.

In discussions of MySQL internal operations, sometimes field is used as a synonym.

See Also , , , , , , , .

An index on a single column.

See Also , .

When an index is created with a length specification, such as CREATE INDEX idx ON t1 (c1(N)), only the first N characters of the column value are stored in the index. Keeping the index prefix small makes the index compact, and the memory and disk I/O savings help performance. (Although making the index prefix too small can hinder query optimization by making rows with different values appear to the query optimizer to be duplicates.)

For columns containing binary values or long text strings, where sorting is not a major consideration and storing the entire value in the index would waste space, the index automatically uses the first N (typically 768) characters of the value to do lookups and sorts.

See Also .

A SQL statement that ends a transaction, making permanent any changes made by the transaction. It is the opposite of rollback, which undoes any changes made in the transaction.

InnoDB uses an optimistic mechanism for commits, so that changes can be written to the data files before the commit actually occurs. This technique makes the commit itself faster, with the tradeoff that more work is required in case of a rollback.

By default, MySQL uses the autocommit setting, which automatically issues a commit following each SQL statement.

See Also , , , , .

The default InnoDB row format since MySQL 5.0.3. Available for tables that use the Antelope file format. It has a more compact representation for nulls and variable-length fields than the prior default (redundant row format).

Because of the B-tree indexes that make row lookups so fast in InnoDB, there is little if any performance benefit to keeping all rows the same size.

For additional information about InnoDB COMPACT row format, see .

See Also , , , .

An index that includes multiple columns.

See Also , .

The compression feature of the MySQL Enterprise Backup product makes a compressed copy of each tablespace, changing the extension from .ibd to .ibz. Compressing the backup data allows you to keep more backups on hand, and reduces the time to transfer backups to a different server. The data is uncompressed during the restore operation. When a compressed backup operation processes a table that is already compressed, it skips the compression step for that table, because compressing again would result in little or no space savings.

A set of files produced by the MySQL Enterprise Backup product, where each tablespace is compressed. The compressed files are renamed with a .ibz file extension.

Applying compression right at the start of the backup process helps to avoid storage overhead during the compression process, and to avoid network overhead when transferring the backup files to another server. The process of applying the binary log takes longer, and requires uncompressing the backup files.

See Also , , , , , .

A row format that enables data and index compression for InnoDB tables. It was introduced in the InnoDB Plugin, available as part of the Barracuda file format. Large fields are stored away from the page that holds the rest of the row data, as in dynamic row format. Both index pages and the large fields are compressed, yielding memory and disk savings. Depending on the structure of the data, the decrease in memory and disk usage might or might not outweigh the performance overhead of uncompressing the data as it is used. See for usage details.

For additional information about InnoDB COMpreSSED row format, see .

See Also , , , .

A feature with wide-ranging benefits from using less disk space, performing less I/O, and using less memory for caching. InnoDB table and index data can be kept in a compressed format during database operation.

The data is uncompressed when needed for queries, and re-compressed when changes are made by DML operations. After you enable compression for a table, this processing is transparent to users and application developers. DBAs can consult information_schema tables to monitor how efficiently the compression parameters work for the MySQL instance and for particular compressed tables.

When InnoDB table data is compressed, the compression applies to the table itself, any associated index data, and the pages loaded into the buffer pool. Compression does not apply to pages in the undo buffer.

The table compression feature requires using MySQL 5.5 or higher, or the InnoDB Plugin in MySQL 5.1 or earlier, and creating the table using the Barracuda file format andcompressed row format, with the innodb_file_per_table setting turned on. The compression for each table is influenced by the KEY_BLOCK_SIZE clause of the and  statements. In MySQL 5.6 and higher, compression is also affected by the server-wide configuration options, , and . See  for usage details.

Another type of compression is the compressed backup feature of the MySQL Enterprise Backup product.

See Also , , , , , , , , , , .

Not actually an error, rather an expensive operation that can occur when using compression in combination with DML operations. It occurs when: updates to a compressed pageoverflow the area on the page reserved for recording modifications; the page is compressed again, with all changes applied to the table data; the re-compressed data does not fit on the original page, requiring MySQL to split the data into two new pages and compress each one separately. To check the frequency of this condition, query the table and check how much the value of the COMpreSS_OPS column exceeds the value of the COMpreSS_OPS_OK column. Ideally, compression failures do not occur often; when they do, you can adjust the configuration options , , and.

See Also , , .

The ability of multiple operations (in database terminology, transactions) to run simultaneously, without interfering with each other. Concurrency is also involved with performance, because ideally the protection for multiple simultaneous transactions works with a minimum of performance overhead, using efficient mechanisms for locking.

See Also , , .

The file that holds the option values used by MySQL at startup. Traditionally, on Linux and UNIX this file is named my.cnf, and on Windows it is named my.ini. You can set a number of options related to InnoDB under the [mysqld] section of the file.

Typically, this file is searched for in the locations /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf and ~/.my.cnf. See  for details about the search path for this file.

When you use the MySQL Enterprise Backup product, you typically use two configuration files: one that specifies where the data comes from and how it is structured (which could be the original configuration file for your real server), and a stripped-down one containing only a small set of options that specify where the backup data goes and how it is structured. The configuration files used with the MySQL Enterprise Backup product must contain certain options that are typically left out of regular configuration files, so you might need to add some options to your existing configuration file for use with MySQL Enterprise Backup.

See Also , .

A read operation that uses snapshot information to present query results based on a point in time, regardless of changes performed by other transactions running at the same time. If queried data has been changed by another transaction, the original data is reconstructed based on the contents of the undo log. This technique avoids some of the locking issues that can reduce concurrency by forcing transactions to wait for other transactions to finish.

With the repeatable read isolation level, the snapshot is based on the time when the first read operation is performed. With the read committed isolation level, the snapshot is reset to the time of each consistent read operation.

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. Because a consistent read does not set any locks on the tables it accesses, other sessions are free to modify those tables while a consistent read is being performed on the table.

For technical details about the applicable isolation levels, see .

See Also , , , , , , , , , , .

An automatic test that can block database changes to prevent data from becoming inconsistent. (In computer science terms, a kind of assertion related to an invariant condition.) Constraints are a crucial component of the ACID philosophy, to maintain data consistency. Constraints supported by MySQL include FOREIGN KEY constraints and unique constraints.

See Also , , , .

A value that is incremented by a particular kind of InnoDB operation. Useful for measuring how busy a server is, troubleshooting the sources of performance issues, and testing whether changes (for example, to configuration settings or indexes used by queries) have the desired low-level effects. Different kinds of counters are available throughperformance_schema tables and information_schema tables, particularly information_schema.innodb_metrics.

See Also , , .

An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than MyISAM can, because InnoDB secondary indexes also include the primary key columns. InnoDB cannot apply this technique for queries against tables modified by a transaction, until that transaction ends.

Any column index or composite index could act as a covering index, given the right query. Design your indexes and queries to take advantage of this optimization technique wherever possible.

See Also , , , .

A type of workload where the primary bottleneck is CPU operations in memory. Typically involves read-intensive operations where the results can all be cached in the buffer pool.

See Also , , , .

MySQL uses the term "crash" to refer generally to any unexpected  operation where the server cannot do its normal cleanup. For example, a crash could happen due to a hardware fault on the database server machine or storage device; a power failure; a potential data mismatch that causes the MySQL server to halt; a fast shutdown initiated by the DBA; or many other reasons. The robust, automatic crash recovery for InnoDB tables ensures that data is made consistent when the server is restarted, without any extra work for the DBA.

See Also , , , , .

The cleanup activities that occur when MySQL is started again after a crash. For InnoDB tables, changes from incomplete transactions are replayed using data from the redo log. Changes that were committed before the crash, but not yet written into the , are reconstructed from the doublewrite buffer. When the database is shut down normally, this type of activity is performed during shutdown by the purge operation.

During normal operation, committed data can be stored in the change buffer for a period of time before being written to the data files. There is always a tradeoff between keeping the data files up-to-date, which introduces performance overhead during normal operation, and buffering the data, which can make shutdown and crash recovery take longer.

See Also , , , , , , , .

Acronym for "create, read, update, delete", a common sequence of operations in database applications. Often denotes a class of applications with relatively simple database usage (basic DDL, DML and query statements in SQL) that can be implemented quickly in any language.

See Also , , , .

An internal data structure that is used to represent the result set of a query, or other operation that performs a search using an SQL WHERE clause. It works like an iterator in other high-level languages, producing each value from the result set as requested.

Although usually SQL handles the processing of cursors for you, you might delve into the inner workings when dealing with performance-critical code.

See Also .

Metadata that keeps track of InnoDB-related objects such as tables, indexes, and table columns. This metadata is physically located in the InnoDB system tablespace. For historical reasons, it overlaps to some degree with information stored in the .frm files.

Because the MySQL Enterprise Backup product always backs up the system tablespace, all backups include the contents of the data dictionary.

See Also , , , , , , .

The directory under which each MySQL instance keeps the data files for InnoDB and the directories representing individual databases. Controlled by the  configuration option.

See Also , .

The files that physically contain the InnoDB table and index data. There can be a one-to-many relationship between data files and tables, as in the case of the system tablespace, which can hold multiple InnoDB tables as well as the data dictionary. There can also be a one-to-one relationship between data files and tables, as when the file-per-table setting is enabled, causing each newly created table to be stored in a separate tablespace.

See Also , , , , , .

A database system or application that primarily runs large queries. The read-only or read-mostly data might be organized in denormalized form for query efficiency. Can benefit from the optimizations for read-only transactions in MySQL 5.6 and higher. Contrast with OLTP.

See Also , , , .

Within the MySQL data directory, each database is represented by a separate directory. The InnoDB system tablespace, which can hold table data from multiple databases within a MySQL instance, is kept in its data files that reside outside the individual database directories. When file-per-table mode is enabled, the .ibd files representing individual InnoDB tables are stored inside the database directories.

For long-time MySQL users, a database is a familiar notion. Users coming from an Oracle Database background will find that the MySQL meaning of a database is closer to what Oracle Database calls a schema.

See Also , , , , , .

Data control language, a set of SQL statements for managing privileges. In MySQL, consists of the  and  statements. Contrast with DDL and DML.

See Also , , .

Data definition language, a set of SQL statements for manipulating the database itself rather than individual table rows. Includes all forms of the CREATE, ALTER, and DROPstatements. Also includes the TRUNCATE statement, because it works differently than a DELETE FROM table_name statement, even though the ultimate effect is similar.

DDL statements automatically commit the current transaction; they cannot be rolled back.

InnoDB‘s  feature enhances performance for , , and many types of  operations. See for more information. Also, the InnoDB‘s  setting can affect the behaviour of  and  operations.

Contrast with DML and DCL.

See Also , , , , , , .

A situation where different transactions are unable to proceed, because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither will ever release the locks it holds.

A deadlock can occur when the transactions lock rows in multiple tables (through statements such as UPDATE or SELECT ... FOR UPDATE), but in the opposite order. A deadlock can also occur when such statements lock ranges of index records and gaps, with each transaction acquiring some locks but not others due to a timing issue.

To reduce the possibility of deadlocks, use transactions rather than LOCK TABLE statements; keep transactions that insert or update data small enough that they do not stay open for long periods of time; when different transactions update multiple tables or large ranges of rows, use the same order of operations (such as SELECT ... FOR UPDATE) in each transaction; create indexes on the columns used in SELECT ... FOR UPDATE and UPDATE ... WHERE statements. The possibility of deadlocks is not affected by the isolation level, because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations.

If a deadlock does occur, InnoDB detects the condition and rolls back one of the transactions (the victim). Thus, even if your application logic is perfectly correct, you must still handle the case where a transaction must be retried. To see the last deadlock in an InnoDB user transaction, use the command SHOW ENGINE INNODB STATUS. If frequent deadlocks highlight a problem with transaction structure or application error handling, run with the  setting enabled to print information about all deadlocks to the mysqld error log.

For background information on how deadlocks are automatically detected and handled, see . For tips on avoiding and recovering from deadlock conditions, see .

See Also , , , , , , , .

A mechanism that automatically detects when a deadlock occurs, and automatically rolls back one of the transactions involved (the victim).

See Also , , , .

When InnoDB processes a DELETE statement, the rows are immediately marked for deletion and no longer are returned by queries. The storage is reclaimed sometime later, during the periodic garbage collection known as the purge operation, performed by a separate thread. For removing large quantities of data, related operations with their own performance characteristics are truncate and drop.

See Also , , .

The technique of storing index changes due to DELETE operations in the insert buffer rather than writing them immediately, so that the physical writes can be performed to minimize random I/O. (Because delete operations are a two-step process, this operation buffers the write that normally marks an index record for deletion.) It is one of the types of change buffering; the others are insert buffering and purge buffering.

See Also , , , , .

A data storage strategy that duplicates data across different tables, rather than linking the tables with foreign keys and join queries. Typically used in data warehouse applications, where the data is not updated after loading. In such applications, query performance is more important than making it simple to maintain consistent data during updates. Contrast withnormalized.

See Also , .

A type of index available with some database systems, where index storage is optimized to process ORDER BY column DESC clauses. Currently, although MySQL allows theDESC keyword in the  statement, it does not use any special storage layout for the resulting index.

See Also .

A page in the InnoDB buffer pool that has been updated in memory, where the changes are not yet written (flushed) to the . The opposite of a clean page.

See Also , , , , .

An operation that retrieves unreliable data, data that was updated by another transaction but not yet committed. It is only possible with the isolation level known as read uncommitted.

This kind of operation does not adhere to the ACID principle of database design. It is considered very risky, because the data could be rolled back, or updated further before being committed; then, the transaction doing the dirty read would be using data that was never confirmed as accurate.

Its polar opposite is consistent read, where InnoDB goes to great lengths to ensure that a transaction does not read information updated by another transaction, even if the other transaction commits in the meantime.

See Also , , , , , , .

A kind of database that primarily organizes data on disk storage (hard drives or equivalent). Data is brought back and forth between disk and memory to be operated upon. It is the opposite of an in-memory database. Although InnoDB is disk-based, it also contains features such as the buffer pool, multiple buffer pool instances, and the adaptive hash indexthat allow certain kinds of workloads to work primarily from memory.

See Also , , .

A type of workload where the primary bottleneck is disk I/O. (Also known as I/O-bound.) Typically involves frequent writes to disk, or random reads of more data than can fit into thebuffer pool.

See Also , , , .

Data manipulation language, a set of SQL statements for performing insert, update, and delete operations. The  statement is sometimes considered as a DML statement, because the SELECT ... FOR UPDATE form is subject to the same considerations for locking as , , and .

DML statements for an InnoDB table operate in the context of a transaction, so their effects can be committed or rolled back as a single unit.

Contrast with DDL and DCL.

See Also , , , , , , .

In the InnoDB full-text search feature, a special column in the table containing the FULLTEXT index, to uniquely identify the document associated with each ilist value. Its name isFTS_DOC_ID (uppercase required). The column itself must be of BIGINT UNSIGNED NOT NULL type, with a unique index named FTS_DOC_ID_INDEX. Preferably, you define this column when creating the table. If InnoDB must add the column to the table while creating a FULLTEXT index, the indexing operation is considerably more expensive.

See Also , , .

InnoDB uses a novel file flush technique called doublewrite. Before writing pages to the data files, InnoDB first writes them to a contiguous area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer have completed, does InnoDB write the pages to their proper positions in the data file. If the operating system crashes in the middle of a page write, InnoDB can later find a good copy of the page from the doublewrite buffer during crash recovery.

Although data is always written twice, the doublewrite buffer does not require twice as much I/O overhead or twice as many I/O operations. Data is written to the buffer itself as a large sequential chunk, with a single fsync() call to the operating system.

To turn off the doublewrite buffer, specify the option .

See Also , , , .

A kind of DDL operation that removes a schema object, through a statement such as  or . It maps internally to an  statement. From an InnoDB perspective, the performance considerations of such operations involve the time that the data dictionary is locked to ensure that interrelated objects are all updated, and the time to update memory structures such as the buffer pool. For a table, the drop operation has somewhat different characteristics than a truncate operation (statement).

See Also , , , , .

A row format introduced in the InnoDB Plugin, available as part of the Barracuda file format. Because TEXT and BLOB fields are stored outside of the rest of the page that holds the row data, it is very efficient for rows that include large objects. Since the large fields are typically not accessed to evaluate query conditions, they are not brought into the buffer poolas often, resulting in fewer I/O operations and better utilization of cache memory.

For additional information about InnoDB DYNAMIC row format, see .

See Also , , , .

A stage similar to beta, when a software product is typically evaluated for performance, functionality, and compatibility in a non-mission-critical setting. InnoDB uses the early adopterdesignation rather than beta, through a succession of point releases leading up to a GA release.

See Also , .

A type of log showing information about MySQL startup and critical runtime errors and crash information. For details, see .

See Also , .

The process of removing an item from a cache or other temporary storage area, such as the InnoDB buffer pool. Often, but not always, uses the LRU algorithm to determine which item to remove. When a dirty page is evicted, its contents are flushed to disk, and any dirty neighbor pages might be flushed also.

See Also , , , .

A kind of lock that prevents any other transaction from locking the same row. Depending on the transaction isolation level, this kind of lock might block other transactions from writing to the same row, or might also block other transactions from reading the same row. The default InnoDB isolation level, REPEATABLE READ, enables higher concurrency by allowing transactions to read rows that have exclusive locks, a technique known as consistent read.

See Also , , , , , , .

A group of pages within a tablespace totaling 1 megabyte. With the default page size of 16KB, an extent contains 64 pages. In MySQL 5.6, the page size can also be 4KB or 8KB, in which case an extent contains more pages, still adding up to 1MB.

InnoDB features such as segments, read-ahead requests and the doublewrite buffer use I/O operations that read, write, allocate, or free data one extent at a time.

See Also , , , , , , .

A file containing the metadata, such as the table definition, of a MySQL table.

For backups, you must always keep the full set of .frm files along with the backup data to be able to restore tables that are altered or dropped after the backup.

Although each InnoDB table has a .frm file, InnoDB maintains its own table metadata in the system tablespace; the .frm files are not needed for InnoDB to operate on InnoDB tables.

These files are backed up by the MySQL Enterprise Backup product. These files must not be modified by an ALTER TABLE operation while the backup is taking place, which is why backups that include non-InnoDB tables perform a FLUSH TABLES WITH READ LOCK operation to freeze such activity while backing up the .frm files. Restoring a backup can result in .frm files being created, changed, or removed to match the state of the database at the time of the backup.

See Also .

A capability first introduced in the InnoDB Plugin, now part of the MySQL server in 5.5 and higher, that speeds up creation of InnoDB secondary indexes by avoiding the need to completely rewrite the associated table. The speedup applies to dropping secondary indexes also.

Because index maintenance can add performance overhead to many data transfer operations, consider doing operations such as ALTER TABLE ... ENGINE=INNODB or INSERT INTO ... SELECT * FROM ... without any secondary indexes in place, and creating the indexes afterward.

In MySQL 5.6, this feature becomes more general: you can read and write to tables while an index is being created, and many more kinds of  operations can be performed without copying the table, without blocking DML operations, or both. Thus in MySQL 5.6 and higher, we typically refer to this set of features as online DDL rather than Fast Index Creation.

For related information, see  and .

See Also , , , .

The default shutdown procedure for InnoDB, based on the configuration setting . To save time, certain flush