Storage Engines are the programs that are integrated with a MYSQL database management to manage the data tables. MYSQL supports different storage engines that handles different tables.
Selection of Storage Engine:
Selection of storage engine, depends on users table type and its purpose. Each has their own advantages and disadvantages. Also, possible to select engine at the time of creating tables.
While selecting storage engine, the factors that can affect your selection are:
- Transaction and concurrency
- Special features
You should use INNODB for your tables unless you have a compelling need to use a different engine!! (To get High Performance)By- Peter Zaitsev
Some of important Storage Engines are :
MyISAM, INNODB, MERGE, MEMORY, etc.
The major thing that one should know what are the difference between InnoDB and MyISAM???
- Designed with a thinking that database are frequently read not updated.
- Simple to understand and implement.
- Has full text searching capability.
- Good for frequent read operations(when several computations are executing simultaneously)
- Faster reads and writes for small to medium sized tables.
- Lock tables while updating.
- Count(*) queries are fast
- Non-transactional. Data loss issues during crashes.
- Table level locking
- Insert and update queries are scaleable, concurrency issues
- Designed with a thinking that database are frequently updated not read.
- Data-integrity is maintained throughout the entire query process.
- Clustering by primary key for faster lookups and ordering.
- Transactional, crash-safe, better online backup capability.
- Allow multiple updates on single table
- Locks only row, not table.
- Slower writes(insert, update queries)
- Slower BLOB handling
- Count(*) queries require full table scan.
Simple comparison chart
|Required full text Search||Yes||5.6+|
|Frequent select queries||Yes|
|Row Locking (multi processing on single table)||Yes|
|Relational base design||Yes|
Frequent reading, almost no writing => MyISAM
Full text search in MySQL <= 5.5 => MyISAM
In all other circumstances, InnoDB is usually the best way to go and INNODB can now use Sphinx for Full Text Searching.
Enjoy Coding! 🙂