Dyntex.net Support Centre: MySQL Reference Manual p.528

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

528 MySQL Technical Reference for Version 4.0.3 MySQL stores its data dictionary information of tables in `.frm' les in database directories. But every InnoDB type 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 a `.frm' le or les, 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' les, and why DROP DATABASE did not work for InnoDB type tables in MySQL versions <= 3.23.43. Every InnoDB table has a special index called the clustered index where the data of the rows is stored. If you dene a PRIMARY KEY on your table, then the index of the primary key will be the clustered index. If you do not dene a primary key for your table, InnoDB will internally generate a clustered index where the rows are ordered by the row id InnoDB assigns to the rows in such a table. The row id is a 6-byte eld which monotonically increases 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 us. In many databases the data is traditionally stored on a dierent page from the index record. If a table is large, the clustered index architecture often saves a disk I/O when compared to the traditional solution. The records in non-clustered indexes (we also call them secondary indexes), in InnoDB 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 will use more space. 7.5.11.1 Physical Structure of an Index All indexes in InnoDB are B-trees where the index records are stored in the leaf pages of the tree. The default size of an index page is 16 kB. When new records are inserted, InnoDB tries to leave 1 / 16 of the page free for future insertions and updates of the index records. If index records are inserted in a sequential (ascending or descending) order, the resulting index pages will be about 15/16 full. If records are inserted in a random order, then the pages will be 1/2 - 15/16 full. If the llfactor of an index page drops below 1/2, InnoDB will try to contract the index tree to free the page. 7.5.11.2 Insert Buering It is a common situation in a database application that the primary key is a unique identier and new rows are inserted in the ascending order of the primary key. Thus the insertions to the clustered index do not require random reads from a disk. On the other hand, secondary indexes are usually non-unique and insertions happen in a relatively random order into secondary indexes. This would cause a lot of random disk I/Os without a special mechanism used in InnoDB.

MySQL Reference Manual

Our Partners

SpiritHost - web hosting for spiritual and education sites
SpiritHit.com - Religious and Spiritual Portal

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide