| The MySQL database application supports several different types of tables (a table's type is also called its storage engine). Although each type supports a different feature set, how you interact with themin terms of running queriesis generally consistent. I'll quickly gloss over the three main types; as you learn more about MySQL, you may want to read the full MySQL manual pages on these as well. The most important table type is MyISAM. MyISAM tables are great for most applications, handling SELECTs and INSERTs very quickly. But the MyISAM storage engine cannot manage transactions, a feature I talk about in Chapter 10. After MyISAM, the two most popular storage engines are InnoDB and MEMORY (which used to be called HEAP). InnoDB tables have been part of the default MySQL installation since version 4.0 (if you are using an earlier version, you must enable InnoDB support; see the MySQL manual). InnoDB tables can be used for transactions and perform UPDATEs nicely. But the InnoDB storage engine is generally slower than MyISAM and requires more disk space on the server. The MEMORY table type is the best performer of the bunch, as such tables store all data in memory, not in files. This comes at a price, as MEMORY tables can only deal with fixed-length column types, have no support for AUTO_INCREMENT, and lose all data in a crash. To choose a storage engine: 1. | Log in to the mysql client.
To choose a storage engine, you should first know what options are available. To do so, you can ask your MySQL server from within the mysql client (see Chapter 2, "Running MySQL," for platform-specific instructions).
| 2. | Determine what storage engines are supported by your version of MySQL by running this query (Figure 4.2): SHOW ENGINES;
The results will differ from one installation of MySQL to the next. This is a good step to take, as there's no point in trying to use a storage engine that's not available.
Figure 4.2. The list of available storage engines for this installation of MySQL. 
| 3. | Decide if you need to support transactions.
Transactions are safer, allowing you to undo some changes and protecting your data in case of a crash. Non-transactional tables are faster, requiring less disk space and memory.
As a rule of thumb, if you need transactions, go with InnoDB. If not, go with MyISAM.
| | | 4. | Determine if you can trade performance for permanence.
If what the table really requires above all is performance, then MEMORY may be your best bet. Interactions with these tables will be very fast, but all data will be lost in a crash!
|
 Tips
The same database can have tables of different types. As an example, an e-commerce database may use MyISAM for storing customer and product data, but use InnoDB for orders (to allow for transactions). Two other popular storage engines are MERGE and BDB (Berkeley Database). The former can treat multiple MyISAM tables as one table. The latter is an alternative to InnoDB, also providing for transactions. The InnoDB storage engine was purchased by Oracle, a rival database company. Oracle also purchased Sleepycat Software, which makes the BDB engine. It is not yet known how this will affect MySQL, but it's entirely possible that both disappear from future versions of the database. This is one reason why it's important to see what storage engines are available by running a SHOW ENGINES query (available as of MySQL 4.0). Each storage engine has different properties: how many indexes are allowed, what kinds of columns can be indexed, how large the table can be (in terms of the size on the server), and so on. If you start using MySQL on this level, read through the associated manual pages for details. If you try to create a table using a storage engine that's not available to that version of MySQL, the server's default storage engine will be used instead.
|
