PDA

View Full Version : mysql> CREATE....... MyISAM -or- InnoDB ?



smokebox
October 25th, 2009, 02:09 PM
This topic is for both a personal application as well as a general discussion.

I'm making a small db with around 2000 entries to start, across 6 tables. I believe I have the tables in a logical and sufficiently normalized form. A majority of the tables will primarily recieve SEARCH queries; while SEARCH, UPDATE, INSERT and DELETEs will occur less frequently on the remaining few.

I'm using MySQL / PHP / XML as a back end for a Flash site, so I'm concerned about response times. But, today is the first day I've read anything on MySQL so I don't know if my concerns are valid. I do know that regardless, Flash seems to take its time parsing XML anyway. That's just something I'll have to play with later down the line.

I have designed the deeper, higher volume 'SEARCH tables' to operate on fixed data types (ENUM, INT, DECIMAL, CHAR), while larger variable data types (VARCHAR, TXT)are accessed less frequently and located on seperate tables.

I did this in hopes of OPTIMIZING for a MyISAM configuration, utilizing techniques such as;
- fixed data type table configurations (when possible)
- minimal null values
- refined multi-column indexes
with a high cardinality (few duplicate values) and low type size
- index majority is INT types and prefixes are used when STRINGs
- and no indexes on extremely small tables.


I've reviewed InnoDB, and have identified the following claimed benefits:
- Foreign Key, improved data integrity due to table relationship constraints
- Improved crash recovery time due to log error checking
- Faster in high volume update environments because of row locking as opposed to table locking.

Foreign Keys is what attracts me most about InnoDB, but maybe thats because I like the increased organization behind it.

I've read conflicting stories on which engine is faster, and under what circumstances. But most seem to agree that the Innodb's increased data integrity alone makes it the obvious choice for any system with important information.

Can any thoughts or observations be shared on this topic? Specifically in regards to the Hostek environment?

Thanks for reading,


__________________________________________________ ____________
Here are a few links I've found informative:

MySQL - Reference Manual 5.0
http://dev.mysql.com/doc/refman/5.0/en/index.html
MySQL 5.0 Reference Manual - Chapter 13.1 Storage Engines
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

MySQL - Database Normalization
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
Wikipedia - Database Normalization
http://en.wikipedia.org/wiki/Database_normalization#Normal_forms

PHP.NET - MySQL Manual
http://us3.php.net/manual/en/book.mysql.php

Egine Performance Comparison
http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

Referential Integrity with Foreign Keys
http://www.databasejournal.com/features/mysql/article.php/2248101/Referential-Integrity-in-MySQL.htm

Max
October 25th, 2009, 10:46 PM
Here at Hostek, we prefer MyISAM because it has some real nice advantages.
We do support InnoDB on our Linux servers, but MyISAM is generally our choice and recommendation.

InnoDB is great for foreign keys.

As far as performance, you truly won't notice a difference between the two. Recovering/moving/restoring MyISAM databases is a lot faster and easier to do. Also if you will be doing full-text searches, you want to use MyISAM for that. MyISAM also has quite a bit smaller memory footprint and uses less disk-space compared to InnoDB.

So our bottom-line recommendation to our customers is if you have a table that is going to be inserted, deleted, and updated A LOT more than it is going to be selected or if you need to use foreign key constraints then you want to use InnoDB. Other then that, stick with MyISAM.