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

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

532 MySQL Technical Reference for Version 4.0.3 7.5.12.3 Defragmenting a Table If there are random insertions or deletions in the indexes of a table, the indexes may become fragmented. By fragmentation we mean that the physical ordering of the index pages on the disk is not close to the alphabetical ordering of the records on the pages, or that there are many unused pages in the 64-page blocks which were allocated to the index. It can speed up index scans if you periodically use mysqldump to dump the table to a text le, drop the table, and reload it from the dump. Another way to do the defragmenting is to ALTER the table type to MyISAM and back to InnoDB again. Note that a MyISAM table must t in a single le on your operating system. If the insertions to and index are always ascending and records are deleted only from the end, then the le space management algorithm of InnoDB guarantees that fragmentation in the index will not occur. 7.5.13 Error Handling The error handling in InnoDB is not always the same as specied in the ANSI SQL stan- dards. According to the ANSI standard, any error during an SQL statement should cause the rollback of that statement. InnoDB sometimes rolls back only part of the statement, or the whole transaction. The following list species the error handling of InnoDB. If you run out of le space in the tablespace, you will get the MySQL 'Table is full' error and InnoDB rolls back the SQL statement. A transaction deadlock or a timeout in a lock wait make InnoDB to roll back the whole transaction. A duplicate key error only rolls back the insert of that particular row, even in a state- ment like INSERT INTO ... SELECT .... This will probably change so that the SQL statement will be rolled back if you have not specied the IGNORE option in your state- ment. A 'row too long' error rolls back the SQL statement. Other errors are mostly detected by the MySQL layer of code, and they roll back the corresponding SQL statement. 7.5.14 Restrictions on InnoDB Tables Warning: do NOT convert MySQL system tables from MyISAM TO InnoDB tables! This is not supported; if you do this MySQL will not restart until you restore the old system tables from a backup or re-generate them with the mysql install db script. SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimisation. If you try to create an unique index on a prex of a column you will get an error:

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