(mysql.info.gz) Table and index

(mysql.info.gz) InnoDB Multi-Versioning
(mysql.info.gz) File space management
 
 15.14 Table and Index Structures
 ================================
 
 MySQL stores its data dictionary information for tables in `.frm' files
 in database directories. This is true for all MySQL storage engines.
 But every `InnoDB' table also has its own entry in `InnoDB' internal
 data dictionaries inside the tablespace. When MySQL drops a table or a
 database, it has to delete both an `.frm' file or files, and the
 corresponding entries inside the `InnoDB' data dictionary.  This is the
 reason why you cannot move `InnoDB' tables between databases simply by
 moving the `.frm' files. It is also the reason why `DROP DATABASE' did
 not work for `InnoDB' type tables before MySQL 3.23.44.
 
 Every `InnoDB' table has a special index called the clustered index
 where the data of the rows is stored. If you define a `PRIMARY KEY' on
 your table, the index of the primary key will be the clustered index.
 
 If you do not define a `PRIMARY KEY' for your table, MySQL picks the
 first `UNIQUE' index that has only `NOT NULL' columns as the primary
 key and `InnoDB' uses it as the clustered index.  If there is no such
 index in the table, `InnoDB' internally generates a clustered index
 where the rows are ordered by the row ID that `InnoDB' assigns to the
 rows in such a table. The row ID is a 6-byte field that increases
 monotonically as new rows are inserted. Thus the rows ordered by the
 row ID will be physically in the insertion order.
 
 Accessing a row through the clustered index is fast because the row data
 will be on the same page where the index search leads.  If a table is
 large, the clustered index architecture often saves a disk I/O when
 compared to the traditional solution.  (In many databases, the data is
 traditionally stored on a different page from the index record.)
 
 In `InnoDB', the records in non-clustered indexes (also called secondary
 indexes) contain the primary key value for the row. `InnoDB' uses this
 primary key value to search for the row from the clustered index. Note
 that if the primary key is long, the secondary indexes use more space.
 
 `InnoDB' compares `CHAR' and `VARCHAR' strings of different lengths
 such that the remaining length in the shorter string is treated as if
 padded with spaces.
(mysql.info.gz) Table and index

Menu
