Home arrow MySQL arrow Page 4 - Optimizing MySQL

Table Types - MySQL

After reading part one and two of our Database Normalization and MySQL series, we could use a little follow up.In this article, we'll discuss those facets of optimizing a MySQL server that relate directly to it's compilation, configuration and subsequent administration.

TABLE OF CONTENTS:
  1. Optimizing MySQL
  2. Compiling and Configuring MySQL
  3. Tuning The Server
  4. Table Types
  5. Even More Optimization...
  6. Conclusion
By: W.J. Gilmore
Rating: starstarstarstarstar / 9
January 29, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
Many MySQL users might be surprised to know that MySQL actually offers five different table types, namely BDB, HEAP, ISAM, MERGE, and MyISAM. The BDB table type falls into a category all by itself, known as transaction-safe. The remaining tables fall into a second category, named non-transaction-safe. Iíll discuss the details of each category and those tables that fall under each category in this section.

Transaction-safe

BDB
The Berkeley DB (BDB) tables are MySQLís transaction-capable tables, developed by Sleepycat Software (http://www.sleepycat.com). These tables provide functionality long-awaited by MySQL users, that is transaction-control. Transaction-control is an extremely valuable function in any RDBMS because it makes it possible to ensure that groups of commands are executed successfully, nullifying the results brought on by the commands if anything happens to go wrong during their execution. As you can imagine, transaction-control is very important in applications such as electronic banking.

Non-transaction-safe

HEAP
HEAP tables are the fastest MySQL table for accessing data. This is because they use a hashed index and are stored in dynamic memory. One very important point to keep in mind about HEAP tables is that if either MySQL or your server crashes, the data is lost!

ISAM
ISAM tables were the previous MySQL default until MyISAM was developed. I would recommend not using this table altogether, and work with the MyISAM table instead.

MERGE
MERGE tables are an interesting new kind of table, made available in Version 3.23.25. A MERGE table is actually a collection of identical MyISAM tables, merged together as one. The motive behind merging several identical tables is largely for efficiency reasons. Doing so can improve speed, searching efficiency, repair efficiency, and save disk space, depending on what youíre attempting to do.

The MERGE tables are still considered beta, but should be officially declared stable very soon.

MyISAM
This is the default MySQL table type. Itís based on the ISAM code, but with several useful extensions. Here are just a few reasons why MyISAM tables are good:
  • MyISAM tables are smaller than ISAM tables, thereby using less resources.
  • MyISAM tables are binary portable across various platforms
  • Larger key sizes, larger key limits.

    There are many advantages to MyISAM. Check out http://www.mysql.com/doc/I/S/ISAM.html for a complete summary of this table.

    Specifying Table Type

    You specify a tableís type at the time of creation. In the following example, Iíll show you how to create a HEAP table:


    mysql>CREATE TABLE email_addresses TYPE=HEAP ( ->email char(55) NOT NULL, ->name char(30) NOT NULL, ->PRIMARY KEY(email) );
    BDB tables require somewhat more configuration. Please refer to http://www.mysql.com/doc/B/D/BDB_overview.html for a complete summary of these tables and what must be done in order to take advantage of them.

    Even More Table Types

    To make your MySQL administration tasks even more interesting, the upcoming MySQL 4.0 will offer two new table types, named Innobase and Gemeni. There isnít too much information yet available about each, so stay tuned.

    There is so much to be learned about the MySQL table types that this brief introduction certainly does not do them any justice. For a more complete introduction, I recommend taking some time to review the information found within the MySQL documentation (http://www.mysql.com).



     
     
    >>> More MySQL Articles          >>> More By W.J. Gilmore
     

    blog comments powered by Disqus
  • escort Bursa Bursa escort Antalya eskort
       

    MYSQL ARTICLES

    - Oracle Unveils MySQL 5.6
    - MySQL Vulnerabilities Threaten Databases
    - MySQL Cloud Options Expand with Google Cloud...
    - MySQL 5.6 Prepped to Handle Demanding Web Use
    - ScaleBase Service Virtualizes MySQL Databases
    - Oracle Unveils MySQL Conversion Tools
    - Akiban Opens Database Software for MySQL Use...
    - Oracle Fixes MySQL Bug
    - MySQL Databases Vulnerable to Password Hack
    - MySQL: Overview of the ALTER TABLE Statement
    - MySQL: How to Use the GRANT Statement
    - MySQL: Creating, Listing, and Removing Datab...
    - MySQL: Create, Show, and Describe Database T...
    - MySQL Data and Table Types
    - McAfee Releases Audit Plugin for MySQL Users

    Developer Shed Affiliates

     


    Dev Shed Tutorial Topics: