|
MySQL MyISAM versus InnoDB usage |
|
|
|
Wednesday, 01 October 2008 |
MySQL MyISAM versus InnoDB usage
MyISAM Table Advantage:
1. High speed logging (mod_log_sql for Apache) - Thousands of record
inserts/second.
2. MyISAM Merge for analyzing across logs - across similar tables -
ideal for logs statistical analysis
3. Listings, i.e. real estate websites, job websites, social networking
listings, product listings, comparison shopping listings, stock
listings.
4. MyISAM and compressed MyISAM (read-only) tables takes much less
space - perfect for read-only DVD.
5. MyISAM can be text search
6. Indexing is much faster.
InnoDB advantage:
1. Any kind of monetary or account transaction where balances are
involved.
2. Referential integrity by using foreign keys. For example, a customer
table with a foreign key "orderID" can be used to remove a customer's
orders when this customer's record is deleted.
3. High read and write at the same time, i.e. stock quotes
Table conversion gotcha:
When MyISAM tables are converted to InnoDB, make sure all "SELECT COUNT
(*) FROM aTable ..." are addressed (or removed) because MyISAM tables
automatically have these counts where InnoDB tables have to scan the
rows.
For scheduled aggregation of data from many different sources where
wholesale cleansing of hundreds of millions of records, use MEMORY type
table for 2 orders of magnitudes faster performance.
|