Learning Mysql

Alternatively, you can use ALTER TABLE
to adjust the type after it’s created. For example,
you could convert the artist table to the InnoDB type:
mysql> ALTER TABLE artist type = InnoDB;
In both examples, you can substitute the alias
ENGINE
for TYPE. Of course, much like every other ALTER TABLE
statement, the overhead of changing your choice can be high for large tables.
Note that there are several, rarely used table types we don’t discuss at all in this book.
These include Merge (which is a variant of MyISAM used in large distributed installations, Example (a nonfunctioning type used to illustrate ideas for programmers, NDB
Cluster (a high-performance type used to partition tables across many computers, Archive (a high-performance, index-free table type used for very large data collections,
CSV (a table type for working with data stored as comma-separated values in text files),
and Federated (a very new engine—added in MySQL 5.0.3—that’s used to store data in remote databases. You can find out more about these under Storage Engines and
Table Types in the MySQL manual.
MyISAM
Before we discuss the alternatives, let’s focus on the default MyISAM type. It’s an all- around performer that’s designed for typical applications it supports very fast querying and has very low overhead for changes to data. It’s also very flexible underneath it adapts how it stores data, depending on the structure of the tables you ask it to create.
You’ll recall from Chapter 6 that we encouraged you to consider using fixed-length column types in preference to variable-length types. It was with MyISAM in mind that we made the recommendation when you use fixed-length fields, MySQL adapts its disk-storage structures for fast data access and modification it’s also easier to recover data from a corrupted table file if it uses fixed-length fields.
One of the key features of MyISAM is its unique way of locking tables. In brief, MyISAM
locks are whole-table locks. This means that when you decide to lock a table, other users can have no access to the table at all. While this seems heavy-handed, it works fine for most typical applications, and management of the locks in this way costs very little memory and computational overhead. We’ll contrast this with other locking schemes later when we describe InnoDB and BDB tables.
Unless you can see a good reason, stick with MyISAM while you’re learning MySQL.
Memory or Heap
Prior to MySQL 4.1, the Memory table type was known as the Heap table type. Both keywords are supported, but the MySQL designers now prefer the term Memory. We’ll use the new term here, but they’re interchangeable.

Download 4.24 Mb.

Share with your friends: