Home arrow MySQL arrow Page 11 - Storage Engine (Table Types)

The Storage Engines - MySQL

This chapter covers the MySQL architecture, locking and concurrency, and transactions. It also discusses how to select the right engine and looks at each of MySQL's storage engines in detail. (From the book High Performance MYSQL: Optimization, Backups, Replication and Load Balancing, by Jeremy Zawodny and Derek Balling, ISBN: 0596-003064, O'Reilly Media, 2004.)

TABLE OF CONTENTS:
  1. Storage Engine (Table Types)
  2. Locking
  3. Multi-Version Concurrency Control
  4. Transactions
  5. Bene
  6. Deadlocks
  7. Transactions in MySQL
  8. Selecting the Right Engine
  9. Practical Examples
  10. Table Conversions
  11. The Storage Engines
  12. MyISAM Tables
  13. Compressed MyISAM Tables
  14. InnoDB Tables
  15. Heap (In-Memory) Tables
By: O'Reilly Media
Rating: starstarstarstarstar / 55
August 02, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Now it’s time to look at each of MySQL’s storage engines in more detail. Table 2-3 summarizes some of the high-level characteristics of the handlers. The following sections provide some basic highlights and background about each table handler as well as any unusual characteristics and interesting features.

Before going further, it’s worth noting that this isn’t an exhaustive discussion of MySQL’s storage engines. We assume that you’ve read (or at least know where to find) the information in the MySQL Reference Manual.

Table 2-5: Storage Engine features in MySQL

Attribute

MyISAM

Heap

BDB

InnoDB

Transactions

No

No

Yes

Yes

Lock granularity

Table

Table

Page (8 KB)

Row

Storage

Split files

In-memory

Single file per table

Tablespace(s)

Isolation levels

None

None

Read committed

All

Portable format

Yes

N/A

No

Yes

Referential integrity

No

No

No

Yes

Primary key with data

No

No

Yes

Yes

MySQL caches data records

No

Yes

Yes

Yes

Availability

All versions

All versions

MySQL-Max

All Versions*

*Prior to MySQL 4.0, InnoDB was available in MySQL-Max only.

Most of MySQL’s disk-based tables have some basic things in common. Each database in MySQL is simply a subdirectory of MySQL’s data directory in the underlying filesystem.* Whenever you create a table, MySQL stores the table definition in a .frm file with the same name as the table. Thus, when you create a table named MyTable, MySQL stores the table definition in MyTable.frm.

* In MySQL 5.0, the term “database” will likely morph into “schema.”

To determine the type of a table, use the SHOW TABLE STATUS command. For example, to examine the user table in the mysql database, you execute the following:

mysql> SHOW TABLE STATUS LIKE 'user' \G

****************** 1. row ***************************
Name: user
Type: MyISAM
Row_format: Dynamic
Rows: 6
Avg_row_length: 59
Data_length: 356
Max_data_length: 4294967295
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2002-01-24 18:07:17
Update_time: 2002-01-24 21:56:29
Check_time: NULL
Create_options:
Comment: Users and global privileges
1 row in set (0.06 sec)

Notice that it’s a MyISAM table. You might also notice a lot of other information and statistics in the output. Let’s briefly look at what each line means:

Name

The table’s name.

Type

The table’s type. Again, in some versions of MySQL, this may say “Engine” rather than “Type.”

Row_format

Dynamic, Fixed, or Compressed. Dynamic rows vary in length because they contain variable-length fields such as VARCHAR or BLOB. Fixed rows, which are always the same size, are made up of fields that don’t vary in length, such as CHAR and INTEGER. Compressed rows exist only in compressed tables (see the later section “Compressed MyISAM”).

Rows

The number of rows in the table. For non-transactional tables, this number is always accurate. For transactional tables, it is usually an estimate.

Avg_row_length

How many bytes the average row contains.

Data_length

How much data (in bytes) the entire table contains.

Max_data_length

The maximum amount of data this table can hold. In a MyISAM table with dynamic (variable length) rows, the index file for a table (tablename.MYI) stores row locations using 32-bit pointers into the data file (tablename.MYD). That means it can address only up to 4 GB of space by default. See the next section, “MyISAM Tables” for more details. For MyISAM tables with fixed-length rows, the limit is just under 4.3 billion rows.

Index_length

How much space is consumed by index data.

Data_free

The amount of space that has been allocated but is currently unused.

Auto_increment

The next AUTO_INCREMENT value.

Create_time

When the table was first created.

Update_time

When data in the table last changed.

Check_time

When the table was last checked using CHECK TABLE or myisamchk.

Create_options

Any other options that were specified when the table was created.

Comment

The comments, if any, that were set when the table was created.

Buy the book!If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!

Visit the O'Reilly Network http://www.oreillynet.com for more online content.



 
 
>>> More MySQL Articles          >>> More By O'Reilly Media
 

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: