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

<< 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

Chapter 7: MySQL Table Types 529 If an index record should be inserted to a non-unique secondary index, InnoDB checks if the secondary index page is already in the buer pool. If that is the case, InnoDB will do the insertion directly to the index page. But, if the index page is not found from the buer pool, InnoDB inserts the record to a special insert buer structure. The insert buer is kept so small that it entirely ts in the buer pool, and insertions can be made to it very fast. The insert buer is periodically merged to the secondary index trees in the database. Often we can merge several insertions on the same page in of the index tree, and hence save disk I/Os. It has been measured that the insert buer can speed up insertions to a table up to 15 times. 7.5.11.3 Adaptive Hash Indexes If a database ts almost entirely in main memory, then the fastest way to perform queries on it is to use hash indexes. InnoDB has an automatic mechanism which monitors index searches made to the indexes dened for a table, and if InnoDB notices that queries could benet from building of a hash index, such an index is automatically built. But note that the hash index is always built based on an existing B-tree index on the table. InnoDB can build a hash index on a prex of any length of the key dened for the B-tree, depending on what search pattern InnoDB observes on the B-tree index. A hash index can be partial: it is not required that the whole B-tree index is cached in the buer pool. InnoDB will build hash indexes on demand to those pages of the index which are often accessed. In a sense, through the adaptive hash index mechanism InnoDB adapts itself to ample main memory, coming closer to the architecture of main memory databases. 7.5.11.4 Physical Record Structure Each index record in InnoDB contains a header of 6 bytes. The header is used to link consecutive records together, and also in the row level locking. Records in the clustered index contain elds for all user-dened columns. In addition, there is a 6-byte eld for the transaction id and a 7-byte eld for the roll pointer. If the user has not dened a primary key for a table, then each clustered index record contains also a 6-byte row id eld. Each secondary index record contains also all the elds dened for the clustered index key. A record contains also a pointer to each eld of the record. If the total length of the elds in a record is < 128 bytes, then the pointer is 1 byte, else 2 bytes. 7.5.11.5 How an Auto-increment Column Works in InnoDB After a database startup, when a user rst does an insert to a table T where an auto- increment column has been dened, and the user does not provide an explicit value for the

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