SpirirHost Support Centre: MySQL Reference Manual p.357

<< 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 5: MySQL Optimisation 357 Deletes will degenerate the table over time (as indexes in nodes are usually not updated on delete). It's harder to cache only the index data. 5.4.2 Get Your Data as Small as Possible One of the most basic optimisation is to get your data (and indexes) to take as little space on the disk (and in memory) as possible. This can give huge improvements because disk reads are faster and normally less main memory will be used. Indexing also takes less resources if done on smaller columns. MySQL supports a lot of dierent table types and row formats. Choosing the right table format may give you a big performance gain. See Chapter 7 [Table types], page 494. You can get better performance on a table and minimise storage space using the techniques listed here: Use the most ecient (smallest) types possible. MySQL has many specialised types that save disk space and memory. Use the smaller integer types if possible to get smaller tables. For example, MEDIUMINT is often better than INT. Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column. Note that if you really need NULL in your application you should denitely use it. Just avoid having it on all columns by default. If you don't have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a xed-size record format is used. This is faster but unfortunately may waste some space. See Section 7.1.2 [MyISAM table formats], page 497. The primary index of a table should be as short as possible. This makes identication of one row easy and ecient. For each table, you have to decide which storage/index method to use. See Chapter 7 [Table types], page 494. Only create the indexes that you really need. Indexes are good for retrieval but bad when you need to store things fast. If you mostly access a table by searching on a combination of columns, make an index on them. The rst index part should be the most used column. If you are always using many columns, you should use the column with more duplicates rst to get better compression of the index. If it's very likely that a column has a unique prex on the rst number of characters, it's better to only index this prex. MySQL supports an index on a part of a character column. Shorter indexes are faster not only because they take less disk space but also because they will give you more hits in the index cache and thus fewer disk seeks. See Section 5.5.2 [Server parameters], page 363. In some circumstances it can be benecial to split into two a table that is scanned very often. This is especially true if it is a dynamic format table and it is possible to use a smaller static format table that can be used to nd the relevant rows when scanning the table.

MySQL Reference Manual

Home
Hosting Plans \| Domain Registration \| About Us \| Contact Us \| Site Map Terms of Use \| Privacy Policy \| Guarantees Merchant Accounts

SpiritHost - web hosting for spiritual and education sites SpiritHit.com - Religious and Spiritual Portal Copyright © 2002 Dyntex Group, Inc. All Rights Reserved

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