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

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

536 MySQL Technical Reference for Version 4.0.3 KEY for you. The hidden key has a length of 5 bytes and is incremented for each insert attempt. If all columns you access in a BDB table are part of the same index or part of the primary key, then MySQL can execute the query without having to access the actual row. In a MyISAM table the above holds only if the columns are part of the same index. The PRIMARY KEY will be faster than any other key, as the PRIMARY KEY is stored to- gether with the row data. As the other keys are stored as the key data + the PRIMARY KEY, it's important to keep the PRIMARY KEY as short as possible to save disk and get better speed. LOCK TABLES works on BDB tables as with other tables. If you don't use LOCK TABLE, MySQL will issue an internal multiple-write lock on the table to ensure that the table will be properly locked if another thread issues a table lock. Internal locking in BDB tables is done on page level. SELECT COUNT(*) FROM table_name is slow as BDB tables doesn't maintain a count of the number of rows in the table. Scanning is slower than with MyISAM tables as one has data in BDB tables stored in B-trees and not in a separate datale. The application must always be prepared to handle cases where any change of a BDB table may make an automatic rollback and any read may fail with a deadlock error. Keys are not compressed to previous keys as with ISAM or MyISAM tables. In other words, the key information will take a little more space in BDB tables compared to MyISAM tables which don't use PACK_KEYS=0. There is often holes in the BDB table to allow you to insert new rows in the middle of the key tree. This makes BDB tables somewhat larger than MyISAM tables. The optimiser needs to know an approximation of the number of rows in the table. MySQL solves this by counting inserts and maintaining this in a separate segment in each BDB table. If you don't issue a lot of DELETE or ROLLBACK statements, this number should be accurate enough for the MySQL optimiser, but as MySQL only stores the number on close, it may be incorrect if MySQL dies unexpectedly. It should not be fatal even if this number is not 100% correct. One can update the number of rows by executing ANALYZE TABLE or OPTIMIZE TABLE. See Section 4.5.2 [ANALYZE TABLE], page 248 . See Section 4.5.1 [OPTIMIZE TABLE], page 247. If you get full disk with a BDB table, you will get an error (probably error 28) and the transaction should roll back. This is in contrast with MyISAM and ISAM tables where mysqld will wait for enough free disk before continuing. 7.6.5 Things we need to x for BDB in the near future: It's very slow to open many BDB tables at the same time. If you are going to use BDB tables, you should not have a very big table cache (like >256) and you should use --no-auto-rehash with the mysql client. We plan to partly x this in 4.0. SHOW TABLE STATUS doesn't yet provide that much information for BDB tables. Optimise performance.

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