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

Selecting the Right Engine - 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

When designing MySQL-based applications, you should decide which engine to use for storing your data. If you donít think about it during the design phase, you will likely face complications later in the process. You might find that the default engine doesnít provide a feature you need, such as transactions. Or maybe the mix of read and write queries your application generates will require more granular locking than MyISAMís table locks.

Because you can make the choice on a table-by-table basis, youíll need a clear idea of how each table is used and the data it stores. Of course, it also helps to have a good understanding of the application as a whole and its potential for growth. Armed with this information, you can begin to make good choices about which table engines can do the job.

Considerations

While there are many factors that can affect your decision, it usually boils down to just a few considerations: transactions and concurrency, backups, and special features.

Transactions and concurrency

When it comes to transactions and concurrency, consider the following guidelines:

  • If your application requires transactions and high read/write concurrency, InnoDB is probably your best bet.

  • If your application requires transactions but only moderate read/write concurrency, either BDB or InnoDB tables should work fine.

  • If your application doesnít require transactions and issues primarily SELECT or primarily INSERT/UPDATE queries, MyISAM is a good choice. Many web applications fall into this category.

Backups

The need to perform regular backups may also influence your table choices. If your server can be shut down at regular intervals for backups, the storage engines are equally easy to deal with. However, if you need to perform online backups in one form or another, the choices become less clear. Chapter 9 deals with this topic in more detail.

Another way of looking at this is simplicity. As youíll see in Chapter 9, using multiple storage engines increases the complexity of backups and server tuning. You may decide that itís just easier to use a single storage engine rather than those that are theoretically best.

Special features

Finally, you sometimes find that an application relies on particular features or optimizations that are provided by only some of MySQLís storage engines. For example, not all tables provide a quick answer to queries like the following:

SELECT COUNT(*) FROM mytable

If your application depends on accurate and fast row counts, MyISAM is the answer. InnoDB must actually count up all the rows, but the MyISAM storage engine always knows the exact row count of a table without the need to do any work.

If your application requires referential integrity with foreign keys, youíre limited to just InnoDB tables. Do you need full-text search capabilities? Only MyISAM tables provide it.

Keep this in mind as you read the more detailed information about each tableís features. There will come a time when you find that the feature you really, really need is available only in one table engine. When that happens, you need to either compromise or break a table into multiple tables of different types.

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: